locked
Connecting to SQL Server with ASP Classic RRS feed

  • Question

  • I am trying to write a connection string to access data in a SQL Server from an ASP file.  Here are some of my efforts and the resulting errors:

    ****************************

    Connect.Open "DRIVER={SQL Server};SERVER=192.168.1.112;DATABASE=[Trip_Denver-OD];Trusted_connection=True;APP=[ASP Script];"

    OR

    Connect.Open"DSN=Trip_Denver-OD_SQL;Database=[Trip_Denver-OD];Trusted_connection=True;"

    Error: -2147217843 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    These attempts use the "trusted_connection" argument.  It apparently can't log on as anonymous.

    ****************************

    Connect.Open "Provider=sqloledb;SERVER=192.168.1.112;DATABASE=[Trip_Denver-OD];UID=my_user_name;PWD=my_password;"

    Error: -2147217843 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'my_user_name'.

    Here I am using my login to the SQL Server computer (aliased here as "my_user_name" and "my_password" for security.)  What user name and password should I use here???? 

    In the Security / Users section of the database, I see a user "DB_User" but I have no idea what the password is for it.  Should I create a new user?  How do I do that?

    I think I'm close here.  I just don't have a valid user name and password for the string.

     

    Thanks!!!!


    b

    Wednesday, August 7, 2013 5:12 PM

Answers

All replies

  • I think you need to create a user in SQL Server first. Make sure that you have the enough permission to create a user.

    Refer

    http://technet.microsoft.com/en-us/library/ms173463.aspx

    http://blog.sqlauthority.com/2008/12/27/sql-server-add-any-user-to-sysadmin-role-add-users-to-system-roles/


    Regards, RSingh



    Wednesday, August 7, 2013 6:24 PM
  • Yes, I think I just need a legit user/pw to include in the connection string.

    ****************

    Following one of the articles you referenced, I went to the Security/Logins folder, right-clicked and chose New Login.  I created a user (Rtd_User1) with a password.  I set the default database to the database to which rights should be limitied (Trip_Denver-OD)

    Here is the current connection string:

    Connect.Open "DRIVER={SQL Server};SERVER=SQL-SERVER82011;DATABASE=Trip_Denver-OD;UID=Rtd_User1;PWD=Rtd01"

    And the resulting error:

    Error: -2147467259 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "Trip_Denver-OD" requested by the login. The login failed.

    (I have tried the database name with and without brackets.)

    ******************

    I have also tried to create a DSN on the web server.  If I use the "With Windows NT authentication using the network login ID" the connection passess the test.  But if I use the "With SQL Server authentification ...", check the "Connect to SQL Server to obtain ..." and put in those credentials, the test fails. 

    When I use this connection string:

    Connect.Open "DSN=Trip_Denver-OD_SQL;Database=[Trip_Denver-OD];UID=Rtd_User1;PWD=Rtd01;"

    I get this error:

    Error: -2147217843 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    I suspect that's because I'm using the "Windows NT authentication" instead of the "Connect to SQL Server"  but the latter doesn't work.

    There MUST be a simple way to do this!  What am I missing???


    b

    Wednesday, August 7, 2013 7:12 PM
  • I think you need to include Integrated Security= True to your connection string.

    Refer the below link:

    http://www.connectionstrings.com/sql-server/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 7, 2013 10:13 PM
    Answerer
  • Well...at least that generates a different error.  So maybe we're getting closer.  Here the current connect string:

    Connect.Open "DRIVER={SQL Server};SERVER=SQL-SERVER82011;initial_catalog=Trip_Denver-OD;DATABASE=Trip_Denver-OD;Integrated Security=True;"

    ...and the resulting error:

    Error: -2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    Is there still something missing?  Any ideas?


    b

    Thursday, August 8, 2013 2:52 PM
  • Refer the below link: You may get some idea:

    http://www.vbforums.com/showthread.php?441355-RESOLVED-Multiple-step-OLE-DB-operation-generated-errors


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 8, 2013 4:22 PM
    Answerer
  • I just read throught that thread.  That guy is having problems connecting to a recordset. I haven't gotten that far yet.  I still haven't connected to the SQL Server.  My problem is with my connection string, not opening a recordset...although the text of the error is similar.

    I still think my issue revolves around the user/password in the string.  I just don't know how to create a new user at the database level (as opposed to the server level.)  I've seen several references to a function that creates users.  I just don't know where to enter it.  When I right click on the database/Security/Users and choose New User, I get a dialog box instead of a code window.  In the dialog box, there's no place to enter a password.

    Where do I create a new user at the database level using T-SQL?

     


    b

    Thursday, August 8, 2013 4:37 PM
  • I just don't know how to create a new user at the database level (as opposed to the server level.) 

    Hello,

    When access to database data in SQL Server instance, We need a Server-level pricipal (Login)  to connect to  SQL Server and a database-level pricipal (database user) to access the data in database. Please refer to the following statement to create a login and database user:
    CREATE LOGIN login_name  WITH PASSWORD = '123';
    USE DB_name
    CREATE USER user_name FOR LOGIN login_name;

    Reference:Accessing SQL Server from a Web Application

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, August 15, 2013 7:33 AM