Connect to your local SQL Server 2012 via TCP/IP

When testing our latest product I ran into the problem that I wanted to use the database running on my workstation but running the site on our local server. It took me a while to get it running but eventually I found the solution. Here I would like to share it with you:

1. Allow remote connections to the SQL Server
Open the Mocrosoft SQL Server Management Studio. Open the server properties and allow remote connections to this server:
Server -> Properties -> Connection

BILD1 500X448

2. Allow TCP/IP on your SQL Server
In the Sql Server Configuration Manager „TCP/IP“ has to be enabled

BILD2 495X245

By right clicking „TCP/IP“ you can open the properties and check the port number (ex. 49172). Most likely the port is blocked in the Windows Firewall. In the next step you see how to open it.

3. Open the firewall for TCP/IP
Open the Windows Firewall and go to
Windows Firewall > Advanced Settings > Inbound Rules > New Rule

BILD3 500X385

Select the following options:
Rule Type: TCP
Protocol and Ports: Port
Specific local ports: 49172
Actions: Allow the connection
Profile: Domain
Name: something like „SQL Server Port 49172“
*BILD4*

4. Change the connection string in your web.config
Finally, change the connection string in your web.config
change
<add name="MY_DB" connectionString="Server=MY_WORKSTATION\SQL12;…>
to
<add name="MY_DB" connectionString="Server=tcp:MY_WORKSTATION,49172;…>

Wir verwenden Cookies zur Bereitstellung von Website-Funktionen und zu Analysezwecken. Mehr zum Datenschutz