Solved

Remote Access to MS SQL Express

Posted on 2011-03-08
3
369 Views
Last Modified: 2012-05-11
Here is my situation:

I am using software that requires a direct database connection to a DB on a hosted server running MS SQL Express 2005. By direct connection, I mean I need to be able to connect via ODBC as a DSN.

The server is a virtual server Windows Server 2003 hosted by a major national host. I've configured the DB to allow for remote connections. I also installed MS SQL Express on my remote machine. How exactly do I connect?

I try using the IP address\SQLEXPRESS and the SQL username/password but no luck. The windows firewall is not configured on the remote machine. Am I missing something here? I assumed that I would be able to connect and access all the tables of the DB just as if I was connected locally.

The error I receive is:

Cannot connect to xx.xx.xx.xx\SQLEXPRESS
Additional informaiton: Login failed for user 'xxxxxx' (Microsoft SQL Server, Error 18456)

Or, is there a better way to connect remotely so that I can establish an ODBC connection via DSB.

Thank you
0
Comment
Question by:youponder
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
devlab2012 earned 500 total points
ID: 35070954
Check that SQL Authentication is enabled. For this, on the remote server open sql express. Right click server node  > Properties > Security > select Sql Server and Windows Authentication.

Expand the Security node. Right click on Logins and create a new login.

Now, use the id/pwd of this newly created account.
0
 

Author Comment

by:youponder
ID: 35072863
That worked - thank you!

I was able to connect via ODBC. Only question is where do I specify the default database that ODBC should connect to.

I've done similar projects with MySQL where the MySQL ODBC driver clearly asks which database to log into. I can't seem to find that in windows.
0
 
LVL 13

Expert Comment

by:devlab2012
ID: 35078797
When you create a ODBC, there is an option of "Change the default database to". See the attached snapshot.
Capture.JPG
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 49
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 48
Replication failure 1 19
SQL- GROUP BY 4 21
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question