WinSQL » Knowledge base

Document information

Document ID:1298
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


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:
  1. Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2008" Program menu
  2. Click on the "Protocols for SQLEXPRESS" node,
  3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable". Refer to the image below

    SQL Server Express Configuration


STEP 2: Configure TCP/IP
  1. Click on the "Protocols for SQLEXPRESS" node
  2. Right click on the "TCP/IP" child node and select properties
  3. Ensure "Enabled" is set to "Yes" and "Listen All" is set to Yes. Refer to the image below.

    SQL Server Express Configuration for TCP/IP

  4. Click the "IP Addresses" tab
  5. Scroll down until you see IPAll
  6. Remove the value for TCP Dynamic Ports
  7. Enter the desired value for TCP Port. For demonstration purposes I am using 5171 for this value. Refer to the image below.

    SQL Server Express Configuration for TCP/IP



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.
  1. Start WinSQL and select "Open ODBC Manager" under the "File" menu
  2. Click "Add" button to add a new User DSN
  3. 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.

    SQL Server Express Configuration for TCP/IP

  4. 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.

    SQL Server Express Configuration for TCP/IP

  5. 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.

    SQL Server Express Configuration for TCP/IP

  6. Click the "Client Configuration" button on the same screen
  7. Uncheck "Dynamically determinate port" and specify a port number. In this example we are using 5171.

    SQL Server Express Configuration for TCP/IP

  8. Click Ok all the way to complete processing
  9. Finally, launch WinSQL and specify the newly created DSN in the connection window.


User comments

Posted by Dusan on 5/19/15 1:42 PM

Your simple and clear instructions worked! Thank you very much!

Posted by Cory on 10/24/15 1:58 PM

Thank you so much :)

Posted by PJ on 6/29/15 8:36 PM

thank you!! excellent

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.

Posted by sol katz on 2/9/16 12:53 PM

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

Posted by arif on 5/27/16 12:28 PM

WIll it work with public IP .... can I create an ODBC by using a public IP address .. ?

Posted by tausif on 11/5/12 2:40 AM

hi admin, i resolve my problem doing such a wonderfull step following your page.thanks sincerely,tausif.

Posted by nimi on 7/10/15 2:04 AM

Thanks it is really useful

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 Donald Jayawardena on 2/25/15 6:00 PM

Thanks very much. After spending several hours on the issue, this solution worked.

Posted by Yeah! The dynamic port was our Problem. Thank you!! on 5/25/16 7:29 AM

Posted by sony on 10/25/16 10:17 AM

i tried this, now also i can't connect to client machine. Please do the needful..

Posted by Wachirapon on 12/1/15 9:26 PM

How I can connect sql server via mobile data network? but via wifi it's work. Sorry for my English. Please help.

Posted by Mike Lee on 7/24/14 12:00 PM

Worked! Many thanks

Posted by sol katz on 2/9/16 12:54 PM

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


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.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com