youponder
asked on
Remote Access to MS SQL Express
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you create a ODBC, there is an option of "Change the default database to". See the attached snapshot.
Capture.JPG
Capture.JPG
ASKER
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.