728x90

sysadmin이 아닌 일반 사용자 계정으로 OPENROWSET 쿼리를 사용하여 원격 데이터 엑세스를 시도하였을 때
엑세스 거부와 관련하여 아래와 같이 정리하였습니다. 보안을 위해 사용하지 않는 것을 권장합니다.


[환경]
SQL Server 2000 SP4

 
[현상]
사용자 계정으로 OPENROWSET 쿼리를 사용하여 원격 데이터 엑세스 할 때 오류 발생  

SELECT * FROM  
  OPENROWSET ('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};
  DBQ=D:\DATA;', 'SELECT * from SELECT * from member.txt')

서버: 메시지 7415, 수준 16, 상태 1, 줄 1
OLE DB 공급자 'MSDASQL'에 대한 임의 액세스가 거부되었습니다. 연결된 서버를 통해 이 공급자에 액세스해야 합니다.

 
[원인]
이 방법은 OLE DB를 사용하여 원격 데이터에 연결하고 액세스하는 일회성의 임시 방법이며 연결된 서버에서
테이블을 액세스하는 방법의 대안입니다. OLE DB 데이터 원본을 자주 참조하려면 연결된 서버를 사용합니다.
지정한 공급자에 대해 DisallowAdhocAccess 레지스트리 옵션을 명시적으로 0으로 설정하고
Ad Hoc Distributed Queries 고급 구성 옵션을 활성화한 경우에만 OPENROWSET를 사용하여 OLE DB
데이터 원본의 원격 데이터에 액세스할 수 있습니다.
임시 이름 사용을 설정하면 SQL Server 2000에 대한 모든 인증된 로그인에서 공급자에 액세스할 수 있습니다.

SQL Server 관리자는 모든 로컬 로그인에서 액세스해도 안전한 신뢰할 수 있는 공급자에 대해서만
이 기능을 설정해야 합니다.
만약 원격 엑세스 허용이 필요하다면 아래 방법으로 연결을 허용할 수 있습니다.

 
[조치방법]
레지스트리를 편집하여 DisallowAdHocAccess Value를 추가합니다.

[시작] - [실행] - REGEDIT.EXE
 
1. MSDASQL

  Locate, and then click the following key in the registry:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL

  On the Edit menu, click Add Value, and then add this registry value:

 Value name: DisallowAdHocAccess
 Data type:  REG_DWORD
 Radix:      Hex
 Value data: 0



2. Microsoft.Jet.OLEDB.4.0

  Locate, and then click the following key in the registry:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0
 
  On the Edit menu, click Add Value, and then add this registry value:

Value name: DisallowAdHocAccess
Data type:  REG_DWORD
Radix:      Hex
Value data: 0



[참고자료]
OPENROWSET(T-SQL)
http://msdn.microsoft.com/ko-kr/library/ms190312.aspx
 
HOW TO: Use the DisallowAdHocAccess Setting to Control Access to Linked Servers
http://support.microsoft.com/kb/327489


작성자 : Lai Go / 작성일자 : 2008.06.15

'데이터베이스 > SQL Server' 카테고리의 다른 글

데이터베이스 시점 복원 방법  (0) 2008.09.29
SQL Server 2005 Cluster Agent Service 실행 실패  (0) 2008.09.29
사용자 데이터베이스 이동 (ALTER DATABASE)  (0) 2008.09.28
시스템 데이터베이스 이동 (msdb,model,tempdb)  (0) 2008.09.28
시스템 데이터베이스 이동 (master 및 리소스 데이터베이스)  (0) 2008.09.28
728x90

ALTER DATABASE 재배치 프로시저를 사용하여 사용자 데이터베이스를 이동하는 방법을 정리하였습니다.

일반적으로 동일한 SQL Server 인스턴스 내에서 파일 이동이 필요할 경우에 이 방법을 사용하며,
만약 다른 인스턴스나  다른 SQL Server로 이동한다면 분리 및 연결(sp_attach_db, sp_detach_db 시스템 저장
프로시져) 방법 또는 백업 및 복원 방법을 이용하면 됩니다.


[시나리오]
1. 데이터베이스명 : LAIGO

2. 변경 전 파일 경로
   1) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\LAIGO.mdf
   2) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\LAIGO_log.ldf

3. 변경 후 파일 경로
   1) D:\Data\laigo.mdf  
   2) D:\Data\laigo_log.ldf


[작업절차]

USE MASTER
GO

-- 1. 파일의 논리 이름을 확인합니다.
SELECT file_id, name, physical_name  FROM sys.master_files WHERE database_id = db_id('LAIGO');
/* LAIGO, LAIGO_LOG */

-- 2. 30초 후 모든 작업을 롤백하고 단일 사용자 모드로 전환합니다.
ALTER DATABASE LAIGO SET SINGLE_USER WITH ROLLBACK AFTER 30


-- 3. LAIGO 데이터베이스 오프라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET OFFLINE


-- 4. mdf,ldf 파일 위치를 변경합니다.


-- 5. mdf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO, FILENAME='d:\data\laigo.mdf')


-- 6. ldf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO_LOG, FILENAME='d:\data\laigo_log.ldf')


-- 7. 데이버테이스를 온라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET ONLINE


--8. 멀티 사용자 모드로 변경합니다.
ALTER DATABASE LAIGO SET MULTI_USER


-- 9. 정상적으로 변경되었는지 확인합니다.
SELECT  name, physical_name  AS CurrentLocation, state_desc  FROM  sys.master_files
WHERE  database_id  = DB_ID(N'LAIGO');


[참고자료]
사용자 데이터베이스 이동
http://msdn.microsoft.com/ko-kr/library/ms345483.aspx

방법: 분리 및 연결을 사용하여 데이터베이스 이동(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms187858.aspx

시스템 데이터베이스 이동
http://technet.microsoft.com/ko-kr/library/ms345408.aspx


작성자 : Lai Go / 작성일자 : 2008.07.29


'데이터베이스 > SQL Server' 카테고리의 다른 글

SQL Server 2005 Cluster Agent Service 실행 실패  (0) 2008.09.29
OPENROWSET 쿼리를 사용하여 원격 데이터 엑세스 실패  (0) 2008.09.29
시스템 데이터베이스 이동 (msdb,model,tempdb)  (0) 2008.09.28
시스템 데이터베이스 이동 (master 및 리소스 데이터베이스)  (0) 2008.09.28
PRIMARY 파일 그룹 용량 부족 현상  (0) 2008.09.28
728x90

SQL Server 2005 시스템 데이터베이스 중 msdb, model, tempdb 에 대해서 데이터 파일과 로그 파일을 이동하는
방법을 아래와 같이 정리하였습니다. (master 및 리소스 데이터베이스는 별도 포스팅할 계획입니다)

msdb, model, tempdb 의 데이터, 로그 파일을 변경하는 방법은 동일합니다.



[msdb 데이터베이스 이동]

1. 데이터베이스 파일의 논리 이름을 확인합니다.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
WHERE database_id = DB_ID(N'msdb');


2. mdf, ldf 파일 위치 정보를 변경합니다.

ALTER DATABASE msdb MODIFY FILE (NAME=MSDBData, FILENAME='D:\Data\msdbdata.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME=MSDBLog, FILENAME='D:\Data\msdblog.ldf')


3. SQL 인스턴스를 중지한 후 파일을 새 위치로 이동합니다.


4. SQL 인스턴스를 시작합니다.


5. 1번에서 사용한 쿼리를 실행하여 변경된 정보를 확인합니다.



[model 데이터베이스 이동]

1. 데이터베이스 파일의 논리 이름을 확인합니다.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
WHERE database_id = DB_ID(N'model');


2. mdf, ldf 파일 위치 정보를 변경합니다.

ALTER DATABASE model MODIFY FILE (NAME=MSDBData, FILENAME='D:\Data\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME=MSDBLog, FILENAME='D:\Data\modellog.ldf')


3. SQL 인스턴스를 중지한 후 파일을 새 위치로 이동합니다.


4. SQL 인스턴스를 시작합니다.


5. 1번에서 사용한 쿼리를 실행하여 변경된 정보를 확인합니다.



[tempdb 데이터베이스 이동]
 

1. 데이터베이스 파일의 논리 이름을 확인합니다.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');


2. mdf, ldf 파일 위치 정보를 변경합니다.

ALTER DATABASE tempdb MODIFY FILE (NAME=MSDBData, FILENAME='D:\Data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME=MSDBLog, FILENAME='D:\Data\templog.ldf')


3. SQL 인스턴스를 중지한 후 다시 시작합니다.
   (SQL Server 인스턴스를 시작할 때마다 tempdb는 자동 생성되므로 파일을 이동할 필요가 없습니다)


4. 1번에서 사용한 쿼리를 실행하여 변경된 정보를 확인합니다.


5. tempdb.mdf, templog.ldf 파일을 원래의 위치에서 삭제합니다.


[참고자료]
시스템 데이터베이스 이동
http://technet.microsoft.com/ko-kr/library/ms345408.aspx


작성자 : Lai Go / 작성일자 : 2008.07.29

'데이터베이스 > SQL Server' 카테고리의 다른 글

OPENROWSET 쿼리를 사용하여 원격 데이터 엑세스 실패  (0) 2008.09.29
사용자 데이터베이스 이동 (ALTER DATABASE)  (0) 2008.09.28
시스템 데이터베이스 이동 (master 및 리소스 데이터베이스)  (0) 2008.09.28
PRIMARY 파일 그룹 용량 부족 현상  (0) 2008.09.28
SQL Server Trace Log 확인  (0) 2008.09.28

+ Recent posts

티스토리툴바