Allow SQL Server to Accept Remote Connections
|Subject:||Configuring SQL Server Express to use TCP/IP for remote connections with ODBC|
For demonstration purposes we will be using the following tools:
- SQL Server Express 2008
- SQL Server ODBC driver
- WinSQL as an ODBC client
IMPORTANT: This article assumes you have enabled SQL Server authentication for client connections.
First, networking protocols are disabled by default in SQL Server Express. Thus, if someone simply installs Express and chooses all the defaults, SQL Server Express will only be able to have connections originating on the local machine where SQL Server is installed.
To enable SQL Server Express to accept remote connections we need to perform the following steps:
STEP 1: Enabling TCP/IP
First we must tell SQL Server Express to listen on TCP/IP, to do this perform the following steps:
- Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2008" Program menu
- Click on the "Protocols for SQLEXPRESS" node,
- Right click on "TCP/IP" in the list of Protocols and choose, "Enable". Refer to the image below
STEP 2: Configure TCP/IP
- Click on the "Protocols for SQLEXPRESS" node
- Right click on the "TCP/IP" child node and select properties
- Ensure "Enabled" is set to "Yes" and "Listen All" is set to Yes. Refer to the image below.
- Click the "IP Addresses" tab
- Scroll down until you see IPAll
- Remove the value for TCP Dynamic Ports
- Enter the desired value for TCP Port. For demonstration purposes I am using 5171 for this value. Refer to the image below.
STEP 3: Configuring the ODBC driver
Following steps can be performed either from the same machine where SQL Server Express is installed or from any other machine.
- Start WinSQL and select "Open ODBC Manager" under the "File" menu
- Click "Add" button to add a new User DSN
- Select "SQL Server" from the list. You might also see another driver called SQL Server Native Drive in the list. This driver, however, only supports Named Pipes as the network protocol and therefore, we do not select that one for this example.
- Next, specify a DNS name and the IP Address or host name of the machine. You can also specify "localhost" if both client and server are on the same machine.
- I leave Windows NT authentication on the next screen. If you running a client on a different operating system, such as Linux or Apple Mac, you will have to use SQL Server authentication.
- Click the "Client Configuration" button on the same screen
- Uncheck "Dynamically determinate port" and specify a port number. In this example we are using 5171.
- Click Ok all the way to complete processing
- Finally, launch WinSQL and specify the newly created DSN in the connection window.
Your simple and clear instructions worked! Thank you very much!
Thank you so much :)
I need to access client server through Winsql, is this requires port number along with server path? I am unable to connect to the client server through Winsql. Is MS SQL server needs to be installed prior to using WinSQL? Please guide on the above.
click and change the default database name from master to the name of your database name
Thanks very much. After spending several hours on the issue, this solution worked.
i tried this, now also i can't connect to client machine. Please do the needful..
I tried it with SQL server 2014 developers edition and it didn't work please advise if we have to do something else Thanks in advance Sol