SQL Server Login works with DSN for ODBC driver 11 for SQL Server but unable to connect through connection string (Classic ASP Application)

0
votes

We have a Classic ASP application hosted on server Win 2008 R2, Database - SQL server 2008 R2. The application was working fine until we disabled TLS 1.0 and enabled TLS 1.2 in app server. TLS 1.2 is enabled on Database server. We have installed SSMS on app server and are able to connect to DB. Created a system DSN and tested using ODBC drivers which is only successful when tried "ODBC driver 11 for SQL Server" driver. Performed UDL test in app server and the test connection failed while using both DSN and connection string with errors "Invalid Connection String Attribute" and "Data source name not found and no default driver specified" respectively. Instead of OLEDB we have tried SQLNCLI10 which also didn't work.

Tried all the drivers and the respective connection-strings, though able to establish connection using "ODBC driver 11 for SQL Server". Hence also tried the connection strings

  1. SQL Server Native Client

    Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

  2. SQL Server for ODBC

    DRIVER={ODBC Driver 11 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password

  3. DSN

    DSN=myDsn;Uid=myUsername;Pwd=;

But unable to establish connection to database. Please suggest.

1
sql-server-2008asp-classicodbctls1.2dsn
If you have Native Client 11 installed then you should use SQLNCLI11 rather than SQLNCLI10 - ie Provider=SQLNCLI11;Server=yourServerAddress;Database=yourDataBase;Uid=yourUsername; Pwd=yourPassword;. The Native Client driver is bundled with SQL Server, there's an OLEDB driver which is included with Windows, so it's usually a good fallback option. The syntax is Provider=SQLOLEDB;Data Source=yourServerAddress;Initial Catalog=yourDataBase; User Id=yourUsername;Password=yourPassword;". Note the use of "Data Source" and "Initial Catalog" in place of "Server" and "Database" - John
Have tried many ODBC drivers, able to connect to Database through DSN only using "ODBC Driver 11 for SQL Server".I need help in connectionstring to connect to database from Classic ASP application through DSN to SQL Server 2008 R2 - Sri

1 Answers

0
votes

This works for my SQL Server 2008 R2 classic ASP application.

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;