Accessing MS SQL databases from classic ASP

I am writing a CMS (content management system) in classic ASP using Microsoft's JScript language (a server-side language based on ECMAScript3) with a database in Microsoft's MS SQL.

Why I am using such an ancient technology as classic ASP/JScript is a topic for another time.  Today I will focus on the steps it took to get it working, and the troubleshooting I had to do along the way.

First of all, you start by instantiating an ADO connection object as follows.
Next, connect to the database using a connection string.

try 

{

var objADO = new ActiveXObject("ADODB.Connection");

var dbConnStr = "Driver={SQL Server};server=MyMachine\\SQLEXPRESS;database=MyDB;uid=MyId;pwd=MyPw;";

objADO.Open(dbConnStr);

... use database ...

}

catch(err)

{

Response.Write(err.description);

}

Note that the code is in a try/catch structure.  The output from err.description helps a lot in diagnosing problems.

First problem

err.description had value:

• [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MyId'.]

The solution to this was found here:

• https://support.microsoft.com/en-us/kb/555332

The problem was that in SQL Server Management Studio I had set the authentication of SQL Server itself to

• Windows Authentication mode

It should be:

• SQL Server and Windows Authentication mode

Second problem

err.description had value:

• [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MyId'.  Reason: The password of the account must be changed.]

The solution to this was found here:

• https://social.msdn.microsoft.com/forums/sqlserver/en-US/3174e9f5-5e51-4d95-89cc-bc81a9f16351/login-failed-for-user-please-what-am-i-doing-wrong

The problem was that I had set following property for the login for MyId in SQL Server Management Studio.

• User must change password at next login

It should be unchecked.

END