title: What connection string and or OLEDB/ODBC drivers when moving "classic ASP/ADO" app from SQL Server 2008 to SQL Server 2017?
body: <p>My "classic ASP" application is on Windows Server 2003 and uses ADO to connect to a SQL Server 2008 database. </p>
  
  <p>The database server is being upgraded from a changing and will be running SQL Server 2017.  </p>
  
  <p>I am trying to prove that the old application can still work, connecting to this newer version of SQL Server. </p>
  
  <p>I have so far not had success in making a connection to a SQL Server 2017 installation from the old WS2003 machine.</p>
  
  <p>I am not sure what OLEDB or ODBC providers or drivers I need to install to get this to work, and ultimately, what the Connection String will need to be. </p>
  
  <p>I have created a small demo program to make the connection using the same code as the real application will. Essentially, all I am doing here is providing a connection string and attempting to connect. This is VB6 using ADO: </p>
  
  <pre><code>Private Sub btnConnect_Click()
  
      Dim theConnection As New ADODB.Connection
  
      theConnection.ConnectionString = Text1.Text 
      theConnection.Open ConnectString
  
      If theConnection.State = 1 Then
          MsgBox ("connected ")
      Else
          MsgBox ("failed to connect ")
      End If
  
  End Sub
  </code></pre>
  
  <p>I can get this program to run on the new server - the one with SQL Server 2017, and it connects successfully.</p>
  
  <p>This connection string works : </p>
  
  <pre><code>Driver={SQL Server Native Client 11.0};Server=192.168.0.23;Database=TestDB;Uid=user1;Pwd=xxx;
  </code></pre>
  
  <p>This also works : </p>
  
  <pre><code>Driver={SQL Server};Server=192.168.0.23;Database=TestDB;Uid=user1;Pwd=xxx;
  </code></pre>
  
  <p>This one is the exact format that the existing code uses to connect. </p>
  
  <p>Now, I understand that for ADO, I have not specified Provider, so I think it defaults to MSDASQL, Microsoft's OLEDB provider for ODBC.</p>
  
  <p>So, to get this to connect from the WS2003 application server, is where I have problems. Is this going to be possible? </p>
  
  <p>I have tried these connection strings with these results : </p>
  
  <pre><code>Driver={SQL Server};Server=&lt;IP address of SQL2008 server&gt;;Database=yyy;Uid=xxx;Pwd=xxx;
  </code></pre>
  
  <p>This WORKS - and this is the existing SQL2008 database.</p>
  
  <pre><code>Driver={SQL Server};Server=&lt;IP address of SQL2017 server&gt;;Database=TestDB;Uid=user1;Pwd=xxx; 
  </code></pre>
  
  <p>This takes approx 20-30 seconds to fail with this message : </p>
  
  <blockquote>
    <p>[ODBC SQL SERVER DRIVER]SQL Server does not exist or access denied</p>
  </blockquote>
  
  <p>Is the "old" {SQL Server} driver going to be able to connect to to SQL2017?</p>
  
  <pre><code>Driver={SQL Server Native Client 11.0};Server=192.168.0.23;Database=TestDB;Uid=user1;Pwd=user1;
  </code></pre>
  
  <p>This fails immediately with the message : </p>
  
  <blockquote>
    <p>[ODBC Driver Manager]Data Source Name not found and no default driver
    specified</p>
  </blockquote>
  
  <p>Does this mean that the {SQL Server Native Client 11.0} ODBC driver is not installed on the WS2003 machine? Can it be installed? </p>
  
  <p>I have tried looking at the various ODBC driver installations from Microsoft here : <a href="https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017</a></p>
  
  <p>However, I'm not sure if I need to be doing this or if they are compatible with WS2003. </p>
  
  <p>Microsoft ODBC Driver 17 for SQL Server : requires at least Windows Server 2008 R2. Same for Microsoft ODBC Driver 13 and 11. I did get the version 11 to install, event though it probably is not supported.  I did use this to successfully create a new ODBC data source to the old SQL2008 database, but not to the SQL2017 database. </p>
  
  <p>What do I need to do to resolve this?</p>