SQL Server connection string

Question

I have moved a site from a Window Server 2003 server to a Window Server 2008 server (64 bit). The site connects to a SQL Server 2005 database. I imported the database, and I am running it under SQL Server 2014 (64bit).

I have the IIS on the Window Server 2008 server running the site. The site runs in classic ASP. I am attempting to determine what connection object type I need to make the classic ASP website connect to the SQL Server 2014 database.

This is the old DSN:

Provider=sqloledb;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=theDatabase;User Id=myID;Password=password

Answer 1

If the application is obfuscating the error message, try connecting with a Powershell script. Perhaps this will provide more information about the cause of the connection error. Even if you don't have the Native Client installed, the legacy SQLOLEDB provider that ships with Windows should still work:

$connection = New-Object -ComObject ADODB.Connection;

Write-Output "Connecting using provider SQLOLEDB";
$connection.Open("Provider=SQLOLEDB; Data Source=localhost\SQLEXPRESS; Initial Catalog=Calendar; User Id=userName; Password=password");

Write-Output "Connecting using provider SQLNCLI11.1";
$connection.Open("Provider=SQLNCLI11.1; Data Source=localhost\SQLEXPRESS; Initial Catalog=Calendar; User Id=userName; Password=password");

To use SQL Server Native Client, check to ensure SQL Server 2012 Native Client is listed as an installed program. It can be downloaded and installed from the Feature Pack downloads: https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065.