Connection string for connecting to a remote server (SQL Server 2008 r2 express)

Question

I need to connect to a server of another machine. I'm using VB6, if that matters.

This is what I've done so far:

1. Enabled Protocols

2. Configured a Fixed Port

3. Opened Ports in the Firewall

Did I missed something here?

This is the connection string that I have been using in the server:

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Enrollment;Data Source=D506D89D71AC41F"

It works fine, but what about the connection string for the client in order to connect to SQL Server found on the server machine?

Sorry I cannot test right now as I am working on a single pc.

Answer 1

As mentioned, it is better to connect to the server by name instead of by IP Address.

The part you probably missed is the SQLBrowser service. SQLBrowser allows you to remap a SQL Server instance name to it's port number, similar to DNS servers for ip addresses.

I would suggest this:

1. Make sure the SQLBrowser service is started. Click Start -> Run, Type Services.msc. Scroll down to "SQL Server Browser". Make sure this service is set to automatic and that it's started.

2. SQL Server Browser uses a udp port for external connections so you will need to open UDP port 1433 in your firewall.

At this point, you should be able to connect using servername\instancename where you have your Data Source parameter in the connection string.

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Enrollment;Data Source=D506D89D71AC41F\Name_Of_Instance"

If you are using a default instance (and it appears as though you are), then you don't need to change your connection string at all.

Answer 2

For the client to connect to the server, you would need to replace data source with a valid URL. Something like:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Enrollment;Data Source=sql.mywebsite.com