How to configure remote access and connect to a remote SQL Server

Allow SQL Server to Accept Remote Connections

Connection / October 8, 2021

SQL Server Express Configuration

Document information

Document ID: 1298
Subject: Configuring SQL Server Express to use TCP/IP for remote connections with ODBC
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.

SQL Server Express Configuration for TCP/IPTo 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

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.
  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/IPSTEP 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.
  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.
  5. I leave Windows NT authentication on the next screen.SQL Server Express Configuration for TCP/IP If you running a client on a different operating system, such as Linux or Apple Mac, you will have to use SQL Server authentication.
  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.
  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

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

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.

Source: web.synametrics.com