title: OPENROWSET fails with "Invalid authorization specification"
body: <p>I am trying to use <code>OPENROWSET</code> in SQL Server 2008 SP1:</p>
  
  <pre><code>SELECT *
  FROM OPENROWSET(
        'SQLOLEDB', 
        'Data Source=hydrogen;User ID=scratch;Password=scratch;',
        'select * from users')
  </code></pre>
  
  <ul>
  <li><strong>OLE DB Provider</strong>: <code>SQLOLEDB</code></li>
  <li><strong>Connection String</strong>: <code>Data Source=hydrogen;User ID=scratch;Password=scratch;</code></li>
  <li><strong>Command Text</strong>: <code>select * from users</code></li>
  </ul>
  
  <p>The query fails with:</p>
  
  <blockquote>
    <p>OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid authorization specification".  </p>
    
    <p>OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid connection string attribute".</p>
    
    <p>Msg 7399, Level 16, State 1, Line 1<br>
    The OLE DB provider "SQLNCLI10" for linked server "(null)" reported an error. Authentication failed.</p>
    
    <p>Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "(null)".</p>
  </blockquote>
  
  <p><strong>Note</strong>: The server and credentials are, of course, correct.</p>
  
  <h2>But i'm not using SQLNCLI10</h2>
  
  <p>The perplexing thing is the error suggests someone is using the OLE DB Provider <strong>SQLNCLI11</strong> <em>(SQL Server Native Client 11.0)</em></p>
  
  <blockquote>
    <p>OLE DB provider "SQLNCLI10" for linked server "(null)" returned message </p>
  </blockquote>
  
  <p>That's not the OLEDB provider i'm using.</p>
  
  <ul>
  <li>i'm not using OLE DB Provider: <code>SQLNCLI10</code> <em>(SQL Server Native Client 11.0)</em></li>
  <li>i'm using OLE DB provider: <code>SQLOLEDB</code> <em>(Microsoft OLE DB Provider for SQL Server)</em></li>
  </ul>
  
  <p>So something is very wrong.</p>
  
  <h2>Random tryings</h2>
  
  <p>Of course we can try random things:</p>
  
  <ul>
  <li><p>specify the provider in the connection string</p>
  
  <pre><code>SELECT *
  FROM OPENROWSET(
        'SQLOLEDB', 
        'Provider=SQLOLEDB;Data Source=hydrogen;User ID=scratch;Password=scratch;',
        'select * from users')
  </code></pre></li>
  <li><p>try using the <code>SQLNCLI10</code> OLE DB provider:</p>
  
  <pre><code>SELECT *
  FROM OPENROWSET(
        'SQLNCLI10', 
        'Data Source=hydrogen;User ID=scratch;Password=scratch;',
        'select * from users')
  </code></pre></li>
  <li><p>try using the <code>SQLNCLI10</code> OLE DB provider:</p>
  
  <pre><code>SELECT *
  FROM OPENROWSET(
        'SQLNCLI10', 
        'Data Source=hydrogen;User ID=scratch;Password=scratch;',
        'select * from users')
  </code></pre></li>
  <li><p>try using <strong>Integrated Security</strong></p>
  
  <pre><code>SELECT *
  FROM OPENROWSET(
        'SQLOLEDB', 
        'Data Source=hydrogen;Integrated Security=SSPI;',
        'select * from users')
  </code></pre></li>
  </ul>
  
  <p>In addition to not working, these attempts don't answer my question:</p>
  
  <blockquote>
    <p>Why is the authentication failing?</p>
  </blockquote>
  
  <h2>More debugging</h2>
  
  <p><strong>Use an invalid provider</strong> - to prove that it is ignoring my provider.</p>
  
  <p>The server seems insistent that i use the <em>"native client"</em>. What happens if i request an invalid OLE DB Provder - for example <code>asdfasf</code>:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Well that makes sense. So it's only <em>sometimes</em> ignoring my provider.</p>
  
  <p><strong>Use a valid non-SQL Server provider</strong>:</p>
  
  <p>What if i use a valid provider that <strong>isn't</strong> SQL Server:</p>
  
  <pre><code>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)".
  </code></pre>
  
  <p>That makes sense; Office isn't installed.</p>
  
  <h2>Connection string properties for provider <strong><code>SQLNCLI11</code></strong></h2>
  
  <p>Property Set <code>DBPROPSET_DBINIT</code></p>
  
  <pre><code>| 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   |
  </code></pre>
  
  <p>Property Set <code>DBPROPSET_SQLSERVERDBINIT</code></p>
  
  <pre><code>| 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 |
  </code></pre>
  
  <h2>Comparison of OLE DB Properties supported by SQLOLEDB and SQLNCLI11</h2>
  
  <p><strong>Property Set:</strong> <code>DBPROPSET_DBINIT</code></p>
  
  <pre><code>| 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       |
  </code></pre>
  
  <p><strong>Property Set:</strong> <code>DBPROPSET_SQLSERVERDBINIT</code></p>
  
  <pre><code>| 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       |
  </code></pre>