Classic Asp Connection String to SQL Server 2012 with Windows Authentication on Windows 8

Question

I'm want to migrate a number of MS Access 2007 Databases to SQL Server 2012 locally but before I do I would like to set up a generic connection string that works, I will the have to get them all up and running on the production server too. This is a sample of the string I have to the .mdb file:

conditions_report = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + Server.MapPath("_private/conditions_report.mdb")
set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = 3
conn.Open conditions_report

And this is about as far as I've come with creating something for the .mdf:

conditions_report = "Provider=SQLNCLI11;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=conditions_report;Data Source=XXX"
set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = 3
conn.Open conditions_report

I only work in MVC these days and have never connected Classic Asp to SQL Server and due to time frames migrating from Classic Asp to MVC is not an option at the moment but this would be a at least a step in the right direction.

Edit

Another problem I'm having is I'm not sure what the error messages are telling me. I'm not sure if they are telling me my server is setup incorrectly, it's a permissions problem, a TCP IP issue, if it's not finding the server or finding the server and not the database and my connection string is just wrong! Some of the errors if have been getting are here:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Named Pipes Provider: Could not open a connection to SQL Server [53].

Cannot open database "conditions_report" requested by the login. The login failed.

TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Any help would be greatly appreciated.

Solution

I think SQLNCLI11 is the preferred driver for SQL Server 2012

http://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/

Comments

[Bojangles]

+1 and thanks John I've made that edit in the code and it's got me one step closer.