MS SQL Server의 설치, 원격 접속, 네트워크의 파일 공유

MS SQL Server를 설치하면 데이터베이스 서비스를 제공할 수 있는 상태가 되고, 실제 데이터베이스 생성/관리 등은 SQL 전용 도구인 SSMS를 통해 접근/관리한다.

물론 클라이언트에서도 SSMS를 이용해서 접속이 가능한데, 이 때는 네트워크상 공유폴더의 파일을 공유할 수 있어야 한다.

 

이 문서는 나 자신의 기억을 붙잡아 두려는 목적으로 작성하므로,  사진을 통한 자세한 설명보다는 가급적 간단히 설명하겠다.

 

1. MS SQL Server 2019 설치

-인스턴스 이름 입력. 별도의 인스턴스 이름 설정이 필요하지 않은 경우, Default로 진행.

-기본 시스템 계정. 이것은 나중에 수정이 필요하나 지금은 건드리지 않음.

 

-계정 설정. 우선 윈도우 인증으로 입력하고 넘어감(추후 설명)

-설치완료. 구성 확인 후, SQL Server 설치를 완료합니다.

 

2.SSMS 설치 (SQL Server Management Studio)

-설치파일 다운로드. SSMS-Setup-KOR.exe 버전 18을 설치함. 이후 버전은 복잡하기만 하여서 포기.

나 같은 초보는 이걸로도 SQL Server 관리가 충분한 듯하다.

-설치 진행. Default로 설치를 마무리함. 재부팅을 하지 않으면 귀챦게 한다.

-설치 확인. 설치된 SSMS를 실행하고 서버에 연결합니다. Server 설치 시 설정했던 '윈도우 인증'으로 접속에 문제가 없을 터이다.

 

3. SSMS 를 통한 SQL Server  원격 접속

위의 1과 2를 통하여 자체컴퓨터에서 SQL Server를 관리할 수가 있다(내부연결). 이 때는 로그인시 '윈도우 인증'으로 충분하다.

그러나 서버 컴퓨터와 클라이언트 컴퓨터가 따로 있는 것이 일반적인 상황이고(외부연결), 이 경우에는 몇가지 처리할 사항이 있다.

 

외부연결을 위하여 먼저 내부 연결로 연결한 후 다음 사항을 처리한다.

1) SQL Server 인증 설정

개체탐색기의 연결을 우클릭하여 [속성]에서 ' SQL Server 및 windows 인증모드'를 선택한다.

2) SQL Server 네트워크 구성 설정

-구성관리자가 안보일 경우 : 윈도우키 + R 실행에서 -> SQLServerManager15.msc 입력 -> 엔터

 

-mssqlserver에 대한 프로토콜 - > TCP/IP를 사용하게 설정한다.

- IP 주소 탭 -> IPAll -> TCP 포트 -> 1433으로 입력 후 확인

 

3) 외부 포트 설정

-제어판에서 방화벽을 찾아들어가서 인바운드규칙을 새로 만들고,

규칙 종류-포트

TCP 적용

특정로컬 포트:1433

연결 허용

적용시기: 도메인, 개인, 공용을 모두 체크

이름과 설명을 적당히 작성한다

 

4. 'SQL Server 및 windows 인증모드' 사용을 위한 로그인 및 사용자 추가

-'윈도우 인증'으로 연결하여 아래 사항을 수행한다.

-개체탐색기의 보안(특정 데이터베이스의 보안이 아님)에서 새 로그인을 추가하고 이름, 암호를 입력.

-암호정책 강제적용, 암호만료 강제 적용을 체크.  [기본데이터베이스]와 [기본언어]는 그대로 둠.

-[이 로그인으로 매핑될 사용자]에서 내가 연결할 데이터베이스를 지정해 준다.

 

연결을 종료하고 ' SQL Server 인증'으로 지금 입력한 사용자와 암호로 로그인하면 연결될 터이다.

 

5. 외부연결시 클라이언트 컴퓨터의 파일을 공유하기 위한 처리

1)구성관리자 >  SQL Server  서비스의 속성 > 로그온 > 기본제공 계정 체크 > '네트워크 서비스' 선택

2) 클라이언트 컴퓨터에서 공유폴더를 만들어 주는데, 공유할 파일은 당연히 공유폴더에 있어야 함.

 

인터넷에 잡다한 의론이 많으나 나는 이것으로 모든 걸 해결하였다.

특히 [네트워크 및 공유센터]의 [고급 공유 설정 변경]은 손대지 않아도 아무 문제가 없었다.

 

6. 외부연결시 클라이언트의 엑셀파일을 액세스하기 위한 준비

accessdatabaseengine_X64.exe를 실행  //이것 때문에 고생이 많았음. 엑셀파일의 액세스에 이것이 필요하다고 말해주는 사람이 많지 않았다. //'microsoft access database engine 2010'로 검색하면 인터넷에서 찾을 수 있다.

'내부연결(윈도우 인증)'로 접속하여 다음을 실행 sp_configure 'show advanced options', 1;  RECONFIGURE;  go  sp_configure 'Ad Hoc Distributed Queries', 1;  RECONFIGURE;  GO  exec master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess',1 go exec master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters',1 go

다음은 클라이언트(NoteBook)의 '공유폴더'에 있는 202408_cdsaccnt.xlsx를 읽는 Query이다. 클라이언트(NoteBook) 컴퓨터의 이름 앞에 \\가 붙는다. 'HDR=Yes'는 엑셀의 특성상 칼럼의 타이틀이 있으므로 데이터로 취급하지 말라는 말이다. cms라는 sheet를 이렇게  [cms$] 로 표현한다. 그러고 보니  sheet를 하나의 테이블처럼 취급하는 듯하다 SELECT    NULL,    NULL,   CONVERT(DATE,SUBSTRING(예정일,1,4)+'-'+SUBSTRING(예정일,5,2)+'-'+SUBSTRING(예정일,7,2), 23),   IIF( 정산일 <>'', CONVERT(DATE, SUBSTRING(정산일,1,4)+'-'+SUBSTRING(정산일,5,2)+'-'+SUBSTRING(정산일,7,2)), NULL),     CAST(출금금액 as numeric),    ISNULL( (SELECT CustNoOfNG FROM Customer C WHERE C.CustNoOfCMS=회원번호), NULL),   회원번호 FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',                 'Excel 12.0; Database=\\NoteBook\공유폴더\202408_cdsaccnt.xlsx; HDR=Yes; IMEX=1',  'SELECT * FROM  [cms$]')

 

외부의 엑셀파일을 액세스하는 연습에 시간이 많이 갔다. 특히 ' SQL Server 인증'과 'windows 인증'을 무수히 오간 노가다를 생각하니 한심하다.

 

엑셀파일을 액세스하게 되어 기쁜 나머지 우선 글을 올려보는데, 빠진 것이 발견되면 수정하겠다.

 

2024년 추석 전일에 탐고루주 씀