Can't connect to database, error 80040e21. Impossible to debug.

Question

I have a simple file that tries to connect to a database-

<%
Set RSDiscounts = Server.CreateObject("ADODB.Recordset")
RSDiscounts.ActiveConnection = "Data Source=serverName;Initial Catalog=dbName.dbo;Integrated Security=True"
%>

When I run it, I get-

 error '80040e21'
 /filename.asp, line 3

Searching for the error code doesn't help. My best guess is that something is specified in the connection string that shouldn't be there. But I used Visual Studio to create the string, and that connects to the database fine.

Is there any way I can figure out what's wrong? This seems like it's impossible to debug.

Answer 1

I think the problem is with your connection string - the string you have there is for ADO.Net but I don't believe that will work with ADODB.

Try a connection string like this:

Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=Yes

Or this is a connection string from one of my old projects with ADODB (from asp classic)

Provider=SQLOLEDB.1;Initial Catalog=databaseName;Data Source=serverName;Trusted Connection=Yes

That may not be 100% right, but you can find more details of all the connection strings you could want at the excellent ConnectionStrings.com.

Answer 2

From the library I wrote :

function SqlServerConnectionString( byval psDataSource, byval psCatalog, byval psUid, byval psPw)
'______________________________________________________________________________
'
'   'Sql Server Connection String'
'______________________________________________________________________________

   dim x
   x = "Provider=MSDASQL.1;Persist Security Info=False;User ID=" & psUid & ";Data Source=" & psDataSource &  ";Initial Catalog=" & psCatalog
   if psPw <> "" then
      x = x & ";pwd=" & psPw
   end if
   SqlServerConnectionString = x

end function

I have similar routines for Firebird, Odbc and Access.

Answer 3

The problem will be you are trying to connect to the database using the user which is running the script. If this is running in IIS it will be something like USR_.

2 alternatives.

1. Give this user access to the database (I wouldn't do this one).

2. Create a SQL user and connect via this.

   PROVIDER=SQLOLEDB;Data Source=serverName;Initial Catalog=dbName.dbo;USER ID=WebUser;PASSWORD=WebUserPassword;