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       |

I'm not terribly surprised SQL Server internally redirects `sqloledb` to `sqlncli`; the former is old, and really intended for legacy clients like ADO. You could simply *not* fight the future and use `OPENROWSET('SQLNCLI','Server=hydrogen;UID=scratch;PWD=scratch', ..)`. The list of valid keywords is [here](https://learn.microsoft.com/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client). If you have a pressing reason to use exactly that connection string, it'll be a little harder. — Jeroen Mostert, Feb 21 '18 at 21:44
Here's a fun factoid: `OPENDATASOURCE` still supports `sqloledb`, old keywords and all. No, I don't know where that discrepancy is coming from. — Jeroen Mostert, Feb 21 '18 at 21:55
@JeroenMostert You'll notice that i tried that. Also note that Microsoft [deprecated SQLNCLI native client](http://archive.is/eslqb), and [recommend you use SQLOLEDB instead](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/support-policies-for-sql-server-native-client#ole-db-support-policies) — Ian Boyd, Feb 22 '18 at 15:27
No, you never tried using `UID` and `PWD` anywhere; you tried to use `User ID` and `Password` with `SQLNCLI`, which will not work. And yes, I am aware of that ominous 7 year old deprecation notice (OLE DB is dead, use ODBC!), but that has itself been [superseded](https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/). The new driver (`msoledbsql`) should not be relevant to your case since you're still on SQL Server 2008, which is out of support anyway. — Jeroen Mostert, Feb 22 '18 at 15:36

score 9 · Answer 1 · answered Mar 22 '18 at 19:44

9

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;

answered Mar 22 '18 at 19:44

Mert

6,432
6
32
68