Last post Jun 29, 2017 05:07 PM by ryanbesko
Jun 29, 2017 03:00 PM|yguyon|LINK
I have the following connection string that used to work for SQL server 9.0 and I'm trying to change it for SQL server 2014 but I have no luck.
SQL server 9.0 - this work
hrDSN ="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xx; password=xx;Initial Catalog=xx; Data Source=xx"
SQL server 2014 - this doesn't work - Not sure what this line should be...
hrDSN ="Provider=SQLOLEDB;Persist Security Info=False;User ID=xx; password=xx;Initial Catalog=xx; Data Source=TG-SQLDEV\SQL2014"
Jun 29, 2017 04:08 PM|PatriceSc|LINK
As usual it is best to always tell which error you have. It seems it could work and the problem is not necessarily with the connection string. For example the first one seems to use a default instance while the 2nd one uses a named instance which might require to
open additional ports in the firewall.
So in general it's always best to start from the error and then see what is wrong rather than to look at a connection string or at your code and wonder what happened WHILE YOU KNOW THIS ALREADY.
Jun 29, 2017 05:07 PM|ryanbesko|LINK
Not sure if this will help you much, but here is the connection string I have in a classic ASP application that is still working today using SQL Server 2016. I have also had it work on 2012, so it seems 2014 would work, too.
This is what is in my global.asa file, part of it generated by Visual InterDev:
'--Project Data Connection
Application("cnThisApp_ConnectionString") = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=POSDB;Data Source=.\;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=9BR8511;Use Encryption for Data=False;Tag with column collation when possible=False;"
Main thing I see is that your string is missing the .1 at the end of the provider value.
EDIT: Also make sure the identity your Application Pool in IIS is running under has permissions to the database, or use the id and password in the connection string as in your examples.