sql server 连接带密码的access

原文出处：http://blog.csdn.net/xcntime/article/details/21303385

特别注意：         在使用openrowset/opendatasource前首先要启用Ad Hoc Distributed Queries服务，因为这个服务不安全所以SqlServer默认是关闭的， 开启方法可以参考：http://blog.csdn.net/chelen_jak/article/details/19087831?locationNum=2&fps=1

如何实现将Access的数据的导入到SQL Server呢，相信网络上有很多教程来帮助大家了。但是，大家发现如果access文档使用了密码加密的话，就会发现网上所介绍的方法失灵了。下面将举例来说明。

以下有数据库abc.mdb，表结构如下：student(id int,name char(10))，密码为test。先要将其导入到sql server当中，假如abc.mdb没有加密，则可以采用下面几种方法：

sql server 导入、更新access的数据（无密码版）

[sql]  view plain  copy

 print ?

1. 1.OpenRowSet方式  
2.   SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0',';database=C:\abc.mdb','select * from student where id=1')  
3.   update OpenRowSet('microsoft.jet.oledb.4.0',';database=C:\abc.mdb','select * from student where id=1') set name='张三'  
4. 2.OpenDataSource方式  
5.   select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\abc.mdb')...student  
6.   update OpenDataSource('microsoft.jet.oledb.4.0','Data Source=C:\abc.mdb')...student set name='张三' WHERE id=1  
7. 3.链接服务器方式  
8.   EXEC sp_addlinkedserver   
9.      @server = N'serTest',   
10.      @provider = N'Microsoft.Jet.OLEDB.4.0',   
11.      @srvproduct = N'OLE DB Provider for Jet',  
12.      @datasrc = N'C:\abc.mdb';  
13.   EXEC sp_addlinkedsrvlogin 'serTest'  
14.   EXEC  sp_helpserver     
15.     --EXEC  sp_dropserver N'serTest'  
16.   go  
17.   UPDATE serTest...student SET name='张三' WHERE id=1  
18.   SELECT * FROM serTest...student WHERE id=1  

很多时候我们的mdb数据库是加密的，那么如果我们仍采用上面的方法，会发现sql执行出错，该怎么办呢？

我们首先查看下msdn中相关函数的定义：

[sql]  view plain  copy

 print ?

1. OPENROWSET   
2. ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'   
3.    | 'provider_string' }   
4.       , {   [ catalog. ] [ schema. ] object   
5.        | 'query'   
6.      }   
7.    | BULK 'data_file' ,   
8.        { FORMATFILE = 'format_file_path' [ <bulk_options> ]  
9.        | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }  
10. } )   
11.   
12. <bulk_options> ::=  
13.    [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]   
14.    [ , ERRORFILE = 'file_name' ]  
15.    [ , FIRSTROW = first_row ]   
16.    [ , LASTROW = last_row ]   
17.    [ , MAXERRORS = maximum_errors ]   
18.    [ , ROWS_PER_BATCH = rows_per_batch ]   
19.   
20.   
21. 参数  
22. 'provider_name'   
23. 字符串，表示在注册表中指定的 OLE DB 访问接口的友好名称（或 PROGID）。provider_name 没有默认值。  
24.   
25. 'datasource'   
26. 与某个特定 OLE DB 数据源相对应的字符串常量。datasource 是将传递给访问接口的 IDBProperties 接口以初始化访问接口的 DBPROP_INIT_DATASOURCE 属性。通常，该字符串包含数据库文件的名称、数据库服务器的名称，或者访问接口能理解的用于定位数据库的名称。  
27.   
28. 'user_id'   
29. 字符串常量，它是传递给指定 OLE DB 访问接口的用户名。user_id 为连接指定安全上下文，并作为 DBPROP_AUTH_USERID 属性传入以初始化访问接口。user_id 不能是 Microsoft Windows 登录名称。  
30.   
31. 'password'   
32. 字符串常量，它是传递给 OLE DB 访问接口的用户密码。在初始化访问接口时，password 作为 DBPROP_AUTH_PASSWORD 属性传入。password 不能是 Microsoft Windows 密码。  
33.   
34. 'provider_string'   
35. 访问接口特定的连接字符串，作为 DBPROP_INIT_PROVIDERSTRING 属性传入以初始化 OLE DB 访问接口。通常 provider_string 封装初始化访问接口所需的所有连接信息。有关 SQL 本机客户端 OLE DB 访问接口可识别的关键字列表，请参阅Initialization and Authorization Properties。  

[sql]  view plain  copy

 print ?

1. OPENDATASOURCE ( provider_name, init_string )  
2.   
3. provider_name   
4. 注册为用于访问数据源的 OLE DB 访问接口的 PROGID 的名称。provider_name 的数据类型为 char，无默认值。  
5.   
6. init_string   
7. 连接字符串，该字符串将要传递给目标提供程序的 IDataInitialize 接口。提供程序字符串语法是以关键字值对为基础的，这些关键字值对由分号隔开，例如：“keyword1=value; keyword2=value”。  
8.   
9. 若要了解提供程序上支持的特定关键字值对，请参阅 Microsoft Data Access SDK。该文档定义了基本语法。下表列出了 init_string 参数中最常用的关键字。   
10.   
11. 关键字  OLE DB 属性  有效值和说明    
12. 扩展属性  
13.  DBPROP_INIT_PROVIDERSTRING  
14.  提供程序特定的连接字符串。  

[sql]  view plain  copy

 print ?

1. sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
2.      [ , [ @provider= ] 'provider_name' ]   
3.      [ , [ @datasrc= ] 'data_source' ]   
4.      [ , [ @location= ] 'location' ]   
5.      [ , [ @provstr= ] 'provider_string' ]   
6.      [ , [ @catalog= ] 'catalog' ]  
7.        
8.        
9.      [ @provstr = ] 'provider_string'  
10. OLE DB 访问接口特定的连接字符串，它可标识唯一的数据源。provider_string 的数据类型为 nvarchar(4000)，默认值为 NULL。provstr 或传递给 IDataInitialize 或设置为 DBPROP_INIT_PROVIDERSTRING 属性以初始化 OLE DB 访问接口。  
11.   
12. 在针对 SQL 本机客户端 OLE DB 访问接口创建链接服务器后，可将 SERVER 关键字用作 SERVER=servername\instancename 来指定实例，以指定特定的 SQL Server 实例。servername 是运行 SQL Server 的计算机名称，instancename 是用户将连接到的特定 SQL Server 实例的名称。   

在网上查询了Microsoft OLE DB Provider for Microsoft Jet的资料：发现对于带有密码的access数据库，要在相应的连接字符串参数中进行标注，文章里面还说明了：mdb数据的密码其实是DBPROPSET_JETOLEDB_DBINIT属性里面的DBPROP_JETOLEDB_DATABASEPASSWORD ，我们可以通过对初始化DBPROP_INIT_PROVIDERSTRING对其进行修改，也就是我们平时所说的连接字符串，这下问题就剩下寻找定义数据库密码属性的关键字了。我们发现，其关键字为Jet OLEDB:Database Password，然而在OpenRowSet函数中，由于微软为了兼容性的考虑吧，其关键字为pwd，貌似采用的是odbc的命名模式？

DBPROP_INIT_PROVIDERSTRING  Description: Extended Properties

DBPROP_JETOLEDB_DATABASEPASSWORD

Type: VT_BSTR Typical R/W: R/W Description: Jet OLEDB:Database Password Password used to open the database. This differs from the user password in that the database password is per file, while a user password is per user.

Microsoft OLE DB Provider for Microsoft Jet Appendix A: Properties IDBDataSourceAdmin for Microsoft Jet Initialization Properties Provider-Defined Properties in DBPROPSET_JETOLEDB_DBINIT OLE DB Initialization Properties: Quick Reference Initialization Property Group

下面公布最终解决方案，红色部分为重点。

  从access文件中读取、修改数据（有密码版）

1.OpenRowSet方式
  SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0',';database=C:\abc.mdb;pwd=test','select * from student where id=1')
  update OpenRowSet('microsoft.jet.oledb.4.0',';database=C:\abc.mdb;pwd=test','select * from student where id=1') set name='张三'
2.OpenDataSource方式
  select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\abc.mdb;Jet OLEDB:Database Password=test')...student
  update OpenDataSource('microsoft.jet.oledb.4.0','Data Source=C:\abc.mdb;Jet OLEDB:Database Password=test')...student set name='张三' WHERE id=1
3.链接服务器方式
  EXEC sp_addlinkedserver 
     @server = N'serTest', 
     @provider = N'Microsoft.Jet.OLEDB.4.0', 
     @srvproduct = N'OLE DB Provider for Jet',
     @datasrc = N'C:\abc.mdb',
     @provstr =N';pwd=test';
  EXEC sp_addlinkedsrvlogin 'serTest'
  EXEC  sp_helpserver   
    --EXEC  sp_dropserver N'serTest'
  Go
  UPDATE serTest...student SET name='张三' WHERE id=1
  SELECT * FROM serTest...student WHERE id=1