SQL Server 프로토콜과 SQLCMD를 사용한 연결

SQL Server 프로토콜과 SQLCMD를 사용한 연결

 

·       Version : SQL Server

 

SQL Server에 연결하는 것은 이미 많은 문서를 통해서 잘 알려져 있으며 GUI를 제공하는 툴들이 많아서 어렵지 않게 느껴질 수가 있다. 하지만 상황에 따라 복잡해 질 수 있다. 만약 TCP 포트에서 수신대기를 하지 않는 SQL Server 인스턴스에는 어떻게 연결할까? 호스트 컴퓨터가 서버  DNS를 확인할 수 없는 경우 어떻게  SQL Server에 연결할까? 명명된 인스턴스는 무엇일까? 이번 포트스에서 SQLCMD를 사용하여 다양한 프로토콜의 의미와 사용법을 알아 본다.

 

SQL Server를 관리하는 다양한 툴들이 있다. 특히SSMS(SQL Server Management Studio)는 Microsoft에서 공식으로 제공하는 SQL Server관리툴이며GUI를 제공한다. 그리고 SQLCMD라는 CLI가 있다. SQLCMD의 경우 명령줄로 이루어져 있다. SQL Server가 Linux를 지원하면서 우리는 GUI 뿐만아니라 쉘 환경에도 익숙해져야 하기 때문에 SQLCMD에 대해 다루어 본다.

 

SSMS를 사용하여 SQL Server 인스턴스에 연결할 때  특정 프로토콜을 사용하기 위해서 Connection Properties 탭에서 프로토콜을 선택할 수 있다.

 

대부분 SQL Server를 연결할 때, 프로토콜을 지정하지 않으면 TCP/IP를 사용한다고 생각할 수 있다. 하지만 프로토콜을 지정하지 않으면 TCP/IP를 사용한다는 보장을 할 수 없다. SSMS및 SQLCMD에는 SQL Server 구성관리자의 프로토콜에 따라 순서가 결정된다.  아래 그림은 SQL Server 구성관리자의 프로토콜 구성이다. 구성관리자에는 32bit, 64bit  두 가지 클라이언트 구성이 있다. SSIS를 사용하는 경우 32bit 커넥터를 사용할 수 있기 때문에 중요하다. 패키자가 64bit 버전에서 실행되고 64bit 설정을 하지 않을 경우 패키지가 실패한다.

 

[SQLCMD를 사용한 SQL Server TCP/IP 연결]

TCP/IP 프로토콜을 사용하여 SQL Server 인스턴스에 연결할 때, 인스턴스가 기본 포트가 아닌 경우 서버 IP 주소, 또는 호스트 이름과 포트가 필요하다. 아래 스크립트는 TCP/IP 를 사용하여 연결하는 일반적인 구문이다.

sqlcmd -S tcp:<computer name>,<port number>

 

IP 주소를 입력하면 TCP/IP 프로토콜을 사용할 것이라는 보장을 할 수 없다. TCP/IP를 보장하기 위해서 tcp:라는 접두어를 사용하여 연결한다. 예를 들어 Windows 인증을 사용하는 SQL Server의 기본 인스턴스를 사용하여 SQL-A라는 서버에 TCP/IP 프로토콜을 사용하여 연결하려는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A sqlcmd –S tcp:SQL-A –U sa –P Pa$$w0rd

 

IP를 사용할 경우 아래 구문을 이용한다.

sqlcmd –S tcp:10.10.10.10 sqlcmd –S tcp:10.10.10.10 –U sa –P Pa$$w0rd

 

명명된 인스턴스를  사용하는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A\TEST sqlcmd –S tcp:SQL-A\TEST –U sa –P Pa$$w0rd

 

포트를 지정해야 할 경우 콤마(,)로 구분하며 포트를 지정해야 한다. 예를 들어 기본 인스턴스가 1433 포트를 사용하고 TEST 인스턴스가 51613포트를 사용하는 경우 연결 문자열을 아래와 같다.

sqlcmd –S tcp:SQL-A,1433 sqlcmd –S tcp:SQL-A,1433 –U sa –P Pa$$w0rd   sqlcmd –S tcp:10.10.10.10,1433 sqlcmd –S tcp:10.10.10.10,1433 –U sa –P Pa$$w0rd   sqlcmd –S tcp:SQL-A,51613 sqlcmd –S tcp:SQL-A,51613 –U sa –P Pa$$w0rd   sqlcmd –S tcp:10.10.10.10,51613 sqlcmd –S tcp:10.10.10.10,51613 –U sa –P Pa$$w0rd

 

 

[SQLCMD와 SQL Server 명명된 파이트 연결]

명명된 파이프는 서버와 클라이언트 간의 통신을 위해 명명된, 단방향, 또는 이중 파이프이다. 내부적으로 명명된 파이프의 모든 인스턴스는 동일한 파이프 이름을 갖지만 메시지 기반 통신과 클라이언트 가장을 허용하는 자체 버퍼를 유지한다. 명명된 파이프는 프로세스간 통신(IPC)에 의존한다. 명명된 파이프를 사용하여 로컬 인스턴스에 연결하면 해당 파이프가 커널 모드에서 로컬 프로시저 호출(LPC)로 실행된다는 점에 유의한다. 일반적으로 프로토콜은 원격 SQL Server 인스턴스에 연결할때 TCP/IP를 사용하는것이 바람직하기 때문에 사용되지 않는다. 반면 로컬 인스턴스에 연결할 때 공유 메모리는 종종 선택되어 사용된다. 아래는 기본 인스턴스의 명명된 파이트 연결구문이다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query

 

예를 들어 서버 MYSERVER의 기본 인스턴스에 대한 명명된 파이프는 다음과 같다.

\\ MYSERVER \ pipe \ sql \ query

 

명명된 인스턴스의 구문은 다음과 같다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query

 

예를 들어 서버 SQL-A에서  SQL Server 인스턴스 TEST에 명명된 파이프를 사용하려는 경우 구문은 아래와 같다.

\\SQL-A\pipe\MSSQL$TEST\sql\query

 

명명된 파이프 프로토콜을 사용하여 SQL Server 인스턴스에 연결하는 것은 TCP/IP를 사용하는것과 크게 다르지 않다. 연결문자열에np: 접두어를 사용한다. 아래 구문은 Windows 및 SQL Server 인증을 사용하여 SQL Server 기본 인스턴스와 명명된 인스턴스에 연결한다.

sqlcmd –S np:\\SQL-A\pipe\sql\query sqlcmd –S np:\\SQL-A\pipe\sql\query –U sa –P Pa$$w0rd sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query –U sa –P Pa$$w0rd

 

 

[SQLCMD를 사용한  SQL Server 공유 메모리 연결]

이 프로토콜은 클라이언트 연결이 로컬 서버에서 실행될 때만 사용될 수 있다. 기본적으로 커널 모드에서 실행되는 로컬 프로시저 호출(LPC)이다. MDAC 2.8 이하를 사용하는 경우 공유 메모리 프로토콜을 사용할 수 없다. 이 경우 sqlcmd가 자동으로 명명된 파이프로 전환한다. 공유 메모리를 사용하는 접두사는 lpc: 이다.

sqlcmd –S lpc:SQL-A sqlcmd –S lpc:SQL-A –U sa –P Pa$$w0rd sqlcmd –S lpc:SQL-A\TEST sqlcmd –S lpc:SQL-A\TEST –U sa –P Pa$$w0rd

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5133/different-ways-to-connect-to-sql-server-using-sqlcmd/

 

 

2019-04-17 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, SSMS, sqlcmd, named pipe, sql server protocol, sql connection, mssql