I am building an ASP application that requires the use of Windows Authentication for permissions in SQL Server 2000. I have turned off anonymous authentication and set up an ODBC System DSN on the server. The System DSN is setup for Authentication with the default database "TEST" selected.
The problem I have is that when I try to run a query in the application it can't find any tables or anything.
(Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name 'TEST.dbo.test'.
I have verified the query in fact works. The default database per the application says "master". (Remembering I set it to "TEST" in ODBC). Even if it was at master, the query should still work if I specify a different database. So what is going on here? I have no evidence of a failed connection. Example of my code to connect, etc:
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB;Integrated Security=SSPI"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Select blah from TEST.dbo.test", conn