sql-server - OPENROWSET 失败，错误为 "Invalid authorization specification"

Question

我正在尝试使用 OPENROWSET在 SQL Server 2008 SP1 中:

SELECT *
FROM OPENROWSET(
      'SQLOLEDB', 
      'Data Source=hydrogen;User ID=scratch;Password=scratch;',
      'select * from users')

OLE DB 提供程序 :SQLOLEDB

连接字符串 :Data Source=hydrogen;User ID=scratch;Password=scratch;

命令文本 :select * from users

查询失败:

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid authorization specification".

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid connection string attribute".

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "(null)" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "(null)".

备注 :当然，服务器和凭据是正确的。

但我没有使用 SQLNCLI10

令人困惑的是错误表明有人正在使用 OLE DB 提供程序 SQLNCLI11 (SQL Server native 客户端 11.0)

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message

那不是我正在使用的 OLEDB 提供程序。

我没有使用 OLE DB 提供程序:SQLNCLI10 (SQL Server Native Client 11.0)

我正在使用 OLE DB 提供程序:SQLOLEDB (用于 SQL Server 的 Microsoft OLE DB 提供程序)

所以有些事情是非常错误的。

随机尝试

当然，我们可以尝试随机的事情:

在连接字符串中指定提供者

SELECT *
FROM OPENROWSET(
      'SQLOLEDB', 
      'Provider=SQLOLEDB;Data Source=hydrogen;User ID=scratch;Password=scratch;',
      'select * from users')

尝试使用 SQLNCLI10 OLE DB 提供程序:

SELECT *
FROM OPENROWSET(
      'SQLNCLI10', 
      'Data Source=hydrogen;User ID=scratch;Password=scratch;',
      'select * from users')

尝试使用 SQLNCLI10 OLE DB 提供程序:

SELECT *
FROM OPENROWSET(
      'SQLNCLI10', 
      'Data Source=hydrogen;User ID=scratch;Password=scratch;',
      'select * from users')

尝试使用 集成安全

SELECT *
FROM OPENROWSET(
      'SQLOLEDB', 
      'Data Source=hydrogen;Integrated Security=SSPI;',
      'select * from users')

除了不起作用之外，这些尝试并没有回答我的问题:

Why is the authentication failing?

更多调试

使用无效的提供程序 - 证明它忽略了我的提供者。

服务器似乎坚持我使用“本地客户端”。如果我请求无效的 OLE DB 提供者会发生什么 - 例如 asdfasf :

SELECT *
FROM OPENROWSET(
      'qqqqqq', 
      'Data Source=hydrogen;User ID=scratch;Password=scratch;',
      'select * from users')

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "qqqqqq" has not been registered.

嗯，这是有道理的。所以它只是有时忽略我的提供者。

使用有效的非 SQL Server 提供程序 :

如果我使用 的有效提供商怎么办不是 SQL 服务器:

SELECT *
FROM OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0', 
      'Data Source=hydrogen;User ID=scratch;Password=scratch;',
      'select * from users')

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

那讲得通;未安装 Office。

提供程序的连接字符串属性 SQLNCLI11

属性(property)集 DBPROPSET_DBINIT

| PropertyID | Description             | Type
|------------|-------------------------|---------|
|          7 | Integrated Security     | VT_BSTR |
|          9 | Password                | VT_BSTR |
|         11 | Persist Security Info   | VT_BOOL | 
|         12 | User ID                 | VT_BSTR |
|         59 | Data Source             | VT_BSTR |
|         60 | Window Handle           | VT_I4   |
|         64 | Prompt                  | VT_I2   |
|         66 | Connect Timeout         | VT_I4   |
|        186 | Locale Identifier       | VT_I4   |
|        160 | Extended Properties     | VT_BSTR |
|        200 | Asynchronous Processing | VT_I4   |
|        233 | Initial Catalog         | VT_BSTR |
|        248 | OLE DB Services         | VT_I4   |
|        284 | General Timeout         | VT_I4   |

属性(property)集 DBPROPSET_SQLSERVERDBINIT

| PropertyID | Description                             | Type    |
|------------|-----------------------------------------|---------|
|          4 | Current Language                        | VT_BSTR |
|          5 | Network Address                         | VT_BSTR |
|          6 | Network Library                         | VT_BSTR |
|          7 | Use Procedure for Prepare               | VT_I4   |
|          8 | Auto Translate                          | VT_BOOL |
|          9 | Packet Size                             | VT_I4   |
|         10 | Application Name                        | VT_BSTR |
|         11 | Workstation ID                          | VT_BSTR |
|         12 | Initial File Name                       | VT_BSTR |
|         13 | Use Encryption for Data                 | VT_BOOL |
|         14 | Replication server name connect option  | VT_BSTR |
|         15 | Tag with column collation when possible | VT_BOOL |
|         16 | MARS Connection                         | VT_BOOL |
|         18 | Failover Partner                        | VT_BSTR |
|         19 | Old Password                            | VT_BSTR |
|         20 | DataTypeCompatibility                   | VT_UI2  |
|         21 | Trust Server Certificate                | VT_BOOL |
|         22 | Server SPN                              | VT_BSTR |
|         23 | Failover Partner SPN                    | VT_BSTR |
|         24 | Application Intent                      | VT_BSTR |

SQLOLEDB 和 SQLNCLI11 支持的 OLE DB 属性比较

属性集: DBPROPSET_DBINIT

| PropertyID | Description             | Type    |  SQLOLEDB |  SQLNCLI11 |
|------------|-------------------------|---------|-----------|------------|
| 7          | Integrated Security     | VT_BSTR |  Yes      |  Yes       |
| 9          | Password                | VT_BSTR |  Yes      |  Yes       |
| 11         | Persist Security Info   | VT_BOOL |  Yes      |  Yes       |
| 12         | User ID                 | VT_BSTR |  Yes      |  Yes       |
| 59         | Data Source             | VT_BSTR |  Yes      |  Yes       |
| 60         | Window Handle           | VT_I4   |  Yes      |  Yes       |
| 64         | Prompt                  | VT_I2   |  Yes      |  Yes       |
| 66         | Connect Timeout         | VT_I4   |  Yes      |  Yes       |
| 160        | Extended Properties     | VT_BSTR |  Yes      |  Yes       |
| 186        | Locale Identifier       | VT_I4   |  Yes      |  Yes       |
| 200        | Asynchronous Processing | VT_I4   |           |  Yes       |
| 233        | Initial Catalog         | VT_BSTR |  Yes      |  Yes       |
| 248        | OLE DB Services         | VT_I4   |  Yes      |  Yes       |
| 284        | General Timeout         | VT_I4   |  Yes      |  Yes       |

属性集: DBPROPSET_SQLSERVERDBINIT

| PropertyID | Description                             | Type    |  SQLOLEDB | SQLNCLI11 |
|------------|-----------------------------------------|---------|-----------|-----------|
| 4          | Current Language                        | VT_BSTR |  Yes      | Yes       |
| 5          | Network Address                         | VT_BSTR |  Yes      | Yes       |
| 6          | Network Library                         | VT_BSTR |  Yes      | Yes       |
| 7          | Use Procedure for Prepare               | VT_I4   |  Yes      | Yes       |
| 8          | Auto Translate                          | VT_BOOL |  Yes      | Yes       |
| 9          | Packet Size                             | VT_I4   |  Yes      | Yes       |
| 10         | Application Name                        | VT_BSTR |  Yes      | Yes       |
| 11         | Workstation ID                          | VT_BSTR |  Yes      | Yes       |
| 12         | Initial File Name                       | VT_BSTR |  Yes      | Yes       |
| 13         | Use Encryption for Data                 | VT_BOOL |  Yes      | Yes       |
| 14         | Replication server name connect option  | VT_BSTR |  Yes      | Yes       |
| 15         | Tag with column collation when possible | VT_BOOL |  Yes      | Yes       |
| 16         | MARS Connection                         | VT_BOOL |           | Yes       |
| 18         | Failover Partner                        | VT_BSTR |           | Yes       |
| 19         | Old Password                            | VT_BSTR |           | Yes       |
| 20         | DataTypeCompatibility                   | VT_UI2  |           | Yes       |
| 21         | Trust Server Certificate                | VT_BOOL |           | Yes       |
| 22         | Server SPN                              | VT_BSTR |           | Yes       |
| 23         | Failover Partner SPN                    | VT_BSTR |           | Yes       |
| 24         | Application Intent                      | VT_BSTR |           | Yes       |

Answer 1

如果您清除连接字符串中的空格，它应该可以工作，

SELECT a.*  
FROM OPENROWSET('SQLNCLI', 'Server=SQLOLEDB;Database=hydrogen;UID=scratch;PWD=scratch',  
     'SELECT * 
      FROM Users') AS a;