In a Winforms application, I set up a connectionstring in my application config file (in an installed application, it would be located under C:\Program Files (x86), called [Your Application].exe.config, or in development, under your development path\bin\Release or \Debug) that looks like this:
<connectionStrings>
<add name=”App.My.MySettings.ConnectionString” connectionString=”Data Source=.\sqlexpress;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password” providerName=”System.Data.SqlClient”/>
</connectionStrings>
This is fine and dandy as long as both the application and the database server are on the same PC.
There will be times where my application would need to be installed on more than just the “host” PC. In other words, I want to be able to install my application on other PCs and still be able to talk to the PC where my database is installed.
No problem, all you have to do is change the application configuration file’s connection string to something like this:
connectionString=”Data Source=hostPCname\sqlexpress;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password“
Not so quick! You’ll most likely come across this exception when you try to run your application on the remote PC:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol)
Below I’ve summarized the steps to take to enable remote connection to the database PC.
On the database host PC, open SQL Server Configuration Manager application. Go to Start -> All Programs -> Microsoft SQ Server XXXX -> Configuration Tools -> SQL Server Configuration Manager
- Configure and enable TCP/IP protocol.
- Right click on TCP/IP protocol and go to Properties.
Under IP Addresses tab, scroll to the bottom in the IPAll section, set TCP Port to a port number you would like. The default SQL Server port is 1433. Apply changes, or click OK and you will be prompted to restart the SQL service. - On the left panel, click SQL Server Services.
If SQL Server (Instance Name) or SQL Server Browser services aren’t already Running, right click on it and choose Start. If they are already running, click Restart.
If Start is grayed out, then click on Properties and go to Service tab, change Start Mode to Automatic or Manual and you will be able to start the service.
- Open up port in Windows Firewall.Click on Start -> Control Panel -> Windows Firewall and click on Advanced Settings
Click on Inbound Rules on the left panel and click on New Rule… on the right
Follow the New Inbound Rule Wizard to set up a firewall port exception
Put in the port you specified when you configure your TCP/IP protocol
For security, only open this port for private network
Give this rule any name you want
- IPv6 vs. IPv4
If all else have been configured and checked out, I learned you may still have connection problems if the client PC is using IPv6. Disable IPv6 in your network configuration and give it a spin
Lastly, you can test your connection (recommend SQL Server Management Studio) to the remote database with this server name:
REMOTEPC,port number\SQLEXPRESS
In your application, something like this:
connectionString=”Data Source=REMOTEPC,port number\SQLEXPRESS;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password”
That’s it!