OPENROWSET fails with "Invalid authorization specification"

Question

I am trying to use OPENROWSET in SQL Server 2008 SP1:

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

• OLE DB Provider: SQLOLEDB
• Connection String: Data Source=hydrogen;User ID=scratch;Password=scratch;
• Command Text: select * from users

The query fails with:

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)".

Note: The server and credentials are, of course, correct.

But i'm not using SQLNCLI10

The perplexing thing is the error suggests someone is using the OLE DB Provider SQLNCLI11 (SQL Server Native Client 11.0)

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

That's not the OLEDB provider i'm using.

• i'm not using OLE DB Provider: SQLNCLI10 (SQL Server Native Client 11.0)
• i'm using OLE DB provider: SQLOLEDB (Microsoft OLE DB Provider for SQL Server)

So something is very wrong.

Random tryings

Of course we can try random things:

• specify the provider in the connection string

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

• try using the SQLNCLI10 OLE DB provider:

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

• try using the SQLNCLI10 OLE DB provider:

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

• try using Integrated Security

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

In addition to not working, these attempts don't answer my question:

Why is the authentication failing?

More debugging

Use an invalid provider - to prove that it is ignoring my provider.

The server seems insistent that i use the "native client". What happens if i request an invalid OLE DB Provder - for example 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.

Well that makes sense. So it's only sometimes ignoring my provider.

Use a valid non-SQL Server provider:

What if i use a valid provider that isn't SQL Server:

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)".

That makes sense; Office isn't installed.

Connection string properties for provider SQLNCLI11

Property Set 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 Set 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 |

Comparison of OLE DB Properties supported by SQLOLEDB and SQLNCLI11

Property Set: 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       |

Property Set: 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

If you clear spaces in the connection string it should work,

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