SQL Server Developer 외부 연결과 DB/DB 사용자 생성하기

"무료 SQL Server Developer 설치하기"글에 이어지는 포스팅입니다. 

앞선 글에서는 무료 DBMS인 SQL Server Developer를 설치하고 DB 인스턴스를 생성한 다음 윈도우 인증으로 해당 데이터베이스 인스턴스에 연결 성공한 것까지 진행했다. 이번에는 외부에서 네트워크로 데이터베이스 인스턴스에 연결하여 개발을 위한 DB를 생성하고 해당 DB에 접근하여 테이블을 생성, 삭제 및 변경할 수 있는 사용자를 추가하는 과정을 다루고자 한다.

 

일단 powershell을 열고 위와 같이 "netstat -a | select-string LISTEN" LISTENING 상태의 포트들 즉, 서비스 상태인 TCP 포트들을 확인할 수 있는데 목록에는 SQL Server의 기본 포트인 1433을 찾을 수 없다. 외부에서 접근할 수 없는 상태이다. 앞선 엔진 설치 과정에서 방화벽 경고가 있었는데 일단 방화벽에서 필요한 포트를 열어주어야 한다.

 

http://go.microsoft.com/fwlink/?LinkId=94001에 따르면 파워셀에서 간단하게 명령으로 작업할 수 있는데 방화벽 룰 추가 명령을 수행하려면 시작메뉴에서 powershell을 입력하고 관리자로 실행을 선택하여 권한을 부여받아야 한다.

 

관리자 권한을 파워쉘을 구동시킨 상태에서 아래의 명령 두 개를 수행하면 필요한 포트인 TCP 1433 및 UDP 1434를 개방할 수 있다.

 

New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

 

방화벽을 개방했으면 SQL Server 구성 관리자(SSCM, SQL Server Configuration Manager)를 열고 SQL Server 네트워크 구성에서 설치 과정에서 생성한 인스턴스 클릭하고 우측에서 TCP/IP 사용을 선택한다.

 

TCP/IP를 더블클릭하거나 속성에서 IPAll부분으로 내려가서 Dynamic ports는 지우고 TCP Ports에 1433을 입력한다.

 

네트워크 설정 다음에는 SQL Server 서비스에서 서버를 재가동시킨다.

 

이 상태에서 다시 TCP 서비스 목록을 조회하면 1433이 LISTENING 중인 것을 확인할 수 있다.

 

이번에는 SSMS에서 SQL Server 인증 모드로 IP 주소와 사용자 아이디 및 비밀번호로 [연결]한다.

 

위의 그림처럼 네트워크를 통해서 SQL Server 인증으로 데이터베이스 시스템에 연결된 것을 확인할 수 있다.

 

방금 로그인한 사용자는 SQL Server의 기본 사용자인 "sa" 이므로 응용 프로그램에서 접속할 수 있는 DB와 DB 사용자를 추가하고 추가한 사용자 아이디가 새로운  DB에 접근할 수 있는 권한을 부여하고자 한다. 시스템 데이터 베이스에서 새 쿼리를 선택하여 쿼리를 통해 일련의 작업을 간편하게 수행할까 한다.

 

 

위의 그림처럼 쿼리 편집 창에 일련의 쿼리를 입력하고 상단의 [실행]를 누르면 로그인 아이디 생성, DB 생성, DB 사용자 생성 및 권한 부여 과정을 한 번에 처리할 수 있다.

 

USE [master];
GO
CREATE LOGIN [prjauser] WITH PASSWORD = N'1234user!', CHECK_EXPIRATION = OFF, CHECK_POLICY = ON;
GO

CREATE DATABASE prjadb;
GO

USE [prjadb];
GO
CREATE USER [prjauser] FOR LOGIN [prjauser];
ALTER ROLE [db_owner] ADD MEMBER [prjauser]; 
GO

 

위의 쿼리에서 로그인 아이디 및 DB 사용자 아이디는 동일하게 "prjauser"로 추가했으므로 적절하게 바꾸어 입력한다. 비밀번호는 영문자, 숫자, 특수문자를 섞어서 입력한다. 사용자 추가 시 비밀번호 유효 기간을 검사하지 않도록 설정했으며 새로 추가한 DB의 이름은 prjadb이므로 용도에 맞게 추가한다. 만약 DB를 여러 개 생성한다면 각 DB에 대한 DB 사용자 추가와 권한 부여 작업도 동일하게 수행한다. 추가한 각 데이터베이스에 대하여 DB 사용자를 추가하며 앞서 추가한 로그인 아이디와 연결하고 추가한 DB 사용자에 DB 소유자 권한을 부여한다. 모든 작업을 수행할 수 있는 권한이다.

 

이제 sa 사용자가 아니라 추가한 사용자 아이디로 SSMS도 연결할 수 있고 DB 접속을 하는 프로그램의 개발 준비도 모두 끝났다.