December 17th, 2003, 10:06 AM
Connecting to SQL Server via ASP...
I have recently just started to get into SQL Server and am having trouble connecting to the database. I have a test database called "MovieMSDE" that i converted over from MS Access, but can't access it. I first tried using the 'sa' user id in my connection string but that didn't work. Then i set up a new account "WebUser" and gave it db_datareaderwriter privileges and tried to connect to the server but can't.
I've tried various methods on the web, as well as a couple in an ASP book i have...i have received some errors:
"user login failed for 'sa'....not a trusted connection"
"user login failed for 'WebUser"..."
"user login failed for DATASOURCE\IUSR_DATASOURCE"
ex 1: "Provider=SQLOLEDB.1;Data Source=ZBOROWSKI;Persist Security Info=False;" & _
"Integrated Security=SSPI;Database=MovieMSDE;User ID=WebUser;Password=testpwd"
i am totally stumped and getting angry w/sql server...
December 17th, 2003, 05:59 PM
My bad! After a day of brainstorming this problem I've come to the realization i'm a bonehead!
I installed SQL Server using integrated windows security. So i re-configured SQL Server to use SQL Server Authentication and now everything works fine. My code:
Dim objConn, objRS
Set objConn = server.CreateObject("ADODB.Connection")
set objRS = server.CreateObject("ADODB.Recordset")
objConn.Provider = "SQLOLEDB.1"
objConn.Properties("Persist Security Info").Value = "False"
objConn.Properties("User ID").Value = "WebUser"
objConn.Properties("Password").Value = "password"
objConn.Properties("Initial Catalog").Value = "MovieMSDE"
objConn.Properties("Data Source").Value = "ZBOROWSKI"