#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Canada
    Posts
    94
    Rep Power
    22

    Connecting to SQL Server via ASP...


    Hi there,

    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"

    any ideas?

    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...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Canada
    Posts
    94
    Rep Power
    22
    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"

    objConn.Open

IMN logo majestic logo threadwatch logo seochat tools logo