|Subject:||Configuring SQL Server Express to use TCP/IP for remote connections with ODBC|
|Creation date:||4/22/10 9:56 AM|
|Last modified on:||4/22/10 1:53 PM|
SQL Express and remote connections
TCP/IP is disable when you first install SQL Server Express on any machine. This KB article discusses how to enable TCP/IP the TCP/IP protocol and use an ODBC driver to connect to SQL Server Express.
For demonstration purposes we will be using the following tools:
- SQL Server Express 2008
- SQL Server ODBC driver
- WinSQL as an ODBC client
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:
STEP 2: Configure TCP/IP
- 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 3: Configuring the ODBC driver
- 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.
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.
Posted by Tope Olusola on 5/14/13 8:10 AM
click and change the default database name from master to the name of your database name
Posted by tausif on 11/5/12 2:40 AM
i resolve my problem doing such a wonderfull step following your page.thanks sincerely,tausif.
Posted by Ramesh Pagar on 11/23/10 10:04 AM
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.
Add a comment to this document
Do you have a helpful tip related to this document that you'd like to share
with other users? Please add it below. Your name and tip will appear at the
end of the document text.