none
Connection String assistance

    Question

  • When using :"Provider=sqloledb;Data Source=SAGESVR;Initial Catalog='ivha';User ID=username;Password=userpassword;Trusted_Connection=False" I get the error -2147217843 logon failed for user'username'

    If I add Integrated Security=SSPI then the connection works fine.  username and userpassword are the same as those used to logon to the network.

    Can someone explain what my problem might be here?

    Thanks

    Friday, July 19, 2013 10:25 AM

Answers

All replies

  • This link may be helpful,

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


    Regards, RSingh

    Friday, July 19, 2013 10:43 AM
  • If I add Integrated Security=SSPI

    When you add that to your connection string, then you logon with your windows credentials to SQL Server, not with the in the connection string specified SQL login = user name + pwd.

    If you get an "logon failed", then because the specified SQL login don't exists. Have you created the SQL server login?


    Olaf Helper

    Blog Xing

    Friday, July 19, 2013 10:46 AM
  • Thanks. I have seen those links, but to be honest think the issue is more likely either the way the user has been created, or an environment issue.
    Friday, July 19, 2013 10:56 AM
  • Thanks Olaf.

    The user has definitely been created as I am testing using the username and password I use to logon to the desktop so they should be identical to those used when adding SSPI to the connection string.

    My hunch is that there is an issue with how the user has been set up either on the domain or on the SQL box which is impacting authentication, but I do not know enough about the admin side to progress that line of thought any further.  Any help in that regard would also be appreciated.

    Friday, July 19, 2013 11:02 AM
  • I am testing using the username and password I use to logon to the desktop so they should be identical to those used when adding SSPI to the connection string.

    That's a missunderstanding; with SSPI the values of user/pwd in the connection string will be ignored.

    Without SSPI the user+pwd in the connection will be used as SQL server authentifictaion; this has nothing to do with the windows credentials.

    Is the SQL Server running in "Mixed mode" = Windows and SQL Server authentification or in "Windows only" mode?


    Olaf Helper

    Blog Xing

    Friday, July 19, 2013 11:20 AM
  • I think I was told it was in mixed mode, but will double check.  Is there a way round the issue if the sql instal is running in windows only mode?
    Friday, July 19, 2013 11:29 AM
  • You can change the auth mode, but this requires a restart of the SQL Server service. See How to: Change Server Authentication Mode

    Olaf Helper

    Blog Xing

    Friday, July 19, 2013 12:27 PM
  • Thanks Olaf
    Friday, July 19, 2013 2:11 PM
  • HI,

    The exact cause here seems to be

    the connection string 

    and to refine further

    FYI

    http://forums.asp.net/post/1370782.aspx

    look there is a different connection pattern for Integrated Security=SSPI  altogether

    and a

    Server keyword and Database keyword for Sql Server Authentication

    Regards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help , or you may vote-up a helpful post



    Friday, July 19, 2013 5:45 PM