none
.Net 2005 - SqlClient.sqlConnection --- Login Failed for user 'domain\User ID'

    Question

  •  

    Currently I am Lost and Clueless...and really could use some help....     (.net 2005)

    Using ADO I am able to connect to SQL Server from outside my network as follows....

    Dim A as New ADODB.Connection
    A.Open("Provider=SQLOLEDB;Initial Catalog=DBName;Integrated Security='';Persist Security Info=False;Data Source=server IP address,winsock tcp/ip port;Network Library=dbmssocn;User ID=domain\user id;Password=password")

    I know you will see something that shouldn't work -- Integrated Security='', but believe it or not, this is the only way it will work....using No or False produces:

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

    So, what the heck.....   To be more mainstream I figured why not employ the Dataset so I can put the datagridview to work, work in memory with the Dataset and update the DB when done...little connection time.....SO......

    I've employed this....

    Dim D As New SqlClient.SqlConnection("Database=THEName;Server=server ip address,winsock tcpip port;Network Library=dbmssocn;User ID=domain\user id;Password=password;Trusted_Connection=False;")

    D.Open()

    I've tried every conceivable variation of this...Integrated Security=no and false...trusted_connection=no..I've taken things out, added things in (even Type System Version=SQL Server 2000Wink....but always come to one very definate result.... I guess this is my lesson in humility.....

    Login failed for user 'domain\user id'

    I mean, it's the same connection to the same DB and server as the ADO connection..."What Could I Possibly Be Missing??????"

    It's apparent I am missing something, but after 4 hours and a million variations of the string above...I am OTF (Out There Flapping)...

    Any Help would be Greatly Appreciated!
    Thanks,
    Tim

    Wednesday, August 20, 2008 11:43 PM

Answers

  • Hi Zlatko,  Thanks for the Post. 

     

    Yes, I removed the UID and PW and noted that the connection succeeded (Windows Authentication)....  I must be getting old, never put that together.  You also identified why I continued to fail connecting to SQL Server (using sql uid and pw) (Not only am I old, but apparently blind as well)....I noted that I still had   domain name\UID; Pwd=PW.....  Removed Domain name\ and bingo...  I appreciate your time, it allowed me to Focus on what I had done as opposed to what I was doing....

    Thank You....

    Have a Great Day,

    Tim

    Thursday, August 21, 2008 11:40 PM
  • It is not a bug it is what the documentation says:

    If DBPROP_AUTH_INTEGRATED is set to a NULL pointer, a null string, or 'SSPI' VT_BSTR value, the SQL Native Client OLE DB provider uses Windows Authentication Mode to authorize user access to the SQL Server database specified by the DBPROP_INIT_DATASOURCE and DBPROP_INIT_CATALOG properties.

    If it is set to VT_EMPTY (the default), SQL Server security is used. The SQL Server login and password are specified in the DBPROP_AUTH_USERID and DBPROP_AUTH_PASSWORD properties.


    Have a look at:
    Initialization and Authorization Properties
    Friday, August 29, 2008 10:36 PM

All replies

  • Windows logins could only be authenticated through Integrated Security. And vice versa - SQL logins could only be authenticated with inline UID and PWD.

     

    What’s happening in your case is Integrated Security=’’ is interpreted as Integrated Security=’SSPI’ in the ADO example. (‘SSPI’ is the only valid value for that setting in ADO. Interpreting ‘’ as ‘SSPI’ looks like a bug to me. I’ll file it right away.) Try Integrated Security=’SSPI’ and drop User ID and Password, and I bet you’ll still get connected. And if you do the same in the SqlClient case (also remove Trusted_Connection), you should also get connected.

     

    Zlatko Michailov

    Program Manager, SQL Server Connectivity

    Microsoft Corp.

     

    If you are satisfied by this post, please mark it as “Answer”.

    Thursday, August 21, 2008 5:40 PM
  • Hi Zlatko,  Thanks for the Post. 

     

    Yes, I removed the UID and PW and noted that the connection succeeded (Windows Authentication)....  I must be getting old, never put that together.  You also identified why I continued to fail connecting to SQL Server (using sql uid and pw) (Not only am I old, but apparently blind as well)....I noted that I still had   domain name\UID; Pwd=PW.....  Removed Domain name\ and bingo...  I appreciate your time, it allowed me to Focus on what I had done as opposed to what I was doing....

    Thank You....

    Have a Great Day,

    Tim

    Thursday, August 21, 2008 11:40 PM
  • It is not a bug it is what the documentation says:

    If DBPROP_AUTH_INTEGRATED is set to a NULL pointer, a null string, or 'SSPI' VT_BSTR value, the SQL Native Client OLE DB provider uses Windows Authentication Mode to authorize user access to the SQL Server database specified by the DBPROP_INIT_DATASOURCE and DBPROP_INIT_CATALOG properties.

    If it is set to VT_EMPTY (the default), SQL Server security is used. The SQL Server login and password are specified in the DBPROP_AUTH_USERID and DBPROP_AUTH_PASSWORD properties.


    Have a look at:
    Initialization and Authorization Properties
    Friday, August 29, 2008 10:36 PM