ms sql 2008 express TCP/IP 이용하여 연결하기

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
   
   

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. 
   
    
   
   

5171 의 원래의 값은 1433 이다. 1433을 이용하면 ODBC에서 바로 연결할 수 있다.

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