locked
ADO/SQL Server Connection problems RRS feed

  • Question

  • I am having problems asking the user for name/password for a connection to SQL Server.

     

    When I use this connection string I can connect to SQL Server: 

     

    cnnConnect.Open "Provider=SQLOLEDB;SERVER=" & SvrName & ";DATABASE=" & dbName & ";Trusted_Connection=Yes"

     

     

    When I use this, I cannont:

    cnnConnect.Open "Provider=SQLOLEDB;SERVER=" & SvrName & ";DATABASE=" & dbName & ";UID=" & UsrName & ";PWD=" & PWD & ""

     

    "loging failed for user -----.----" error message

     

     

    We don't have SQL user roles, we use windows authentication.

     

    John

    Tuesday, May 29, 2007 1:00 PM

Answers

  • You don't need to ask for the userName and password if it's in Windows Athentication Mode. The SQL Server can identify which user has initiated the current Windows session. If you are trying to assign different access levels to different windows users, you can do so using the Server Security node in Management Studio. For example, you can deny any command execution, deletion, insertion, etc to a user "xUser". The security node contains logins and server roles that you can play with.

     

    I think that is what you wanted. If not, please explain why you want the username and password.

    Tuesday, May 29, 2007 3:47 PM

All replies

  • The second connectioString would work in SQL athentication mode. If the userName is not a Windows user, it will fail. The first string works in Windows athentication mode.
    Tuesday, May 29, 2007 2:04 PM
  • My server is in windows authentication mode and I want to ask the user to enter his/her name & password when they execute this procedrue.  How can I do this in win authentication mode?

     

     

    Tuesday, May 29, 2007 2:09 PM
  • You don't need to ask for the userName and password if it's in Windows Athentication Mode. The SQL Server can identify which user has initiated the current Windows session. If you are trying to assign different access levels to different windows users, you can do so using the Server Security node in Management Studio. For example, you can deny any command execution, deletion, insertion, etc to a user "xUser". The security node contains logins and server roles that you can play with.

     

    I think that is what you wanted. If not, please explain why you want the username and password.

    Tuesday, May 29, 2007 3:47 PM