Connecting Remotely to SQL Express

[tfhwargt3]

Hello All,

I have successfully connected to my SQLExpress server with a connection string locally on my machine. However, now I am trying to connect remotely to the server from withing my network and I am having some issues.

My connection string locally that works looks like so:

conSQL.ConnectionString = "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=C:\Program Files....\MSSQL\Data\datasql.mdf; Database=datasql;Trusted_Connection=Yes;MARS_Connection=yes;"

The connection string I am using for remote access is:

conSQL.ConnectionString = "Driver={SQL Server};" & _
"Server=192.168.1.16;" & _
"Address=192.168.1.16,1433;" & _
"Network=DBMSSOCN;" & _
"Database=datasql;" & _
"Uid=sa;" & _
"Pwd=xxxxxxxxxxxxxxx"

The Error I am getting is:

Run-time error '-2147467259 (800004005)':
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] SQL Server does not exist or access denied.

I have turned on TCP/IP access for the server and also restarted the server afterwards to make sure all changes are taking place. Does anyone know why this error is occurring or why I can't connect?

Thanks for any help.

 

[bborissov]

You have named instance of the SQL Server, so try:

conSQL.ConnectionString="Driver={SQL Native Client};" & _
                        "Server=192.168.1.16\SQLExpress;"& _
                        "Database=datasql;" & _
                        "Uid=sa;Pwd=xxxxxx;"

Also make sure that the remote connection is allowed for that instance of SQL Server. Run "SQL Server Surface Area Configuration" and check there.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[tfhwargt3]

Thank you bborissov. Unfortunately your idea gave the same error. I figured out what does work by checking another application that connects to my database remotely and was already working. I check its connection string through some options screens and found that it was using {SQL SERVER} as the drive and not {SQL NATIVE CLIENT} as we had both tried.

I substituted this for the driver field in the connection string and voila. It works great!

Hope this helps someone along the way.