locked
SQL Connection String RRS feed

  • Question

  • Hey all,

    I am having an issue with a Connection String.  Here is the string:

     

    myConnection = new SqlConnection("Initial Catalog=myDB;Data Source=IPofServer;User ID=LocalUserwithDBOaccess;Password=pw;");

     

    However, I can not get the connection to go through.  When I look at the logs it says:

     

    2011-02-22 09:42:00.53 Logon       Error: 18456, Severity: 14, State: 38.
    2011-02-22 09:42:00.53 Logon       Login failed for user 'Domain\UserName'. Reason: Failed to open the explicitly specified database. [CLIENT: IP]

     

    The domain account that it is listing is my account which also has DBO access to the database.  The two funny things is, my account has access and it is not even trying to use the SQL account that I set up in the string.  What would cause this issue or do I have something wrong with my connection string?

     

    Thanks,

    Matt

    Tuesday, February 22, 2011 4:59 PM

Answers

  • okay, so i should have forgotted to read something important here, I just noted that this is in your webservice? Remember IIS handles security in a different way:

    Few things to note here:

    For ASP.NET 2.0 applications, you should store connection strings in the <connectionStrings> section of your application's Web.config file. The connection string used with Windows authentication must include either the Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI, as shown here.

    <connectionStrings>
     <add name="MyDbConn1" 
        connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
     <add name="MyDbConn2" 
       connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
    </connectionStrings> 
    
    Next there are two primary things:
    • Using the Network Service Account
    • Using a Custom Service Account
    Refer: http://msdn.microsoft.com/en-us/library/ff647396.aspx

    Balaji Baskar
    http://codesupport.wordpress.com
    Click on "Vote As Helpful" and "Mark As Answer" if this has helped you.
    • Proposed as answer by Balaji Baskar Wednesday, February 23, 2011 2:59 PM
    • Marked as answer by Matt11380 Wednesday, February 23, 2011 3:19 PM
    Tuesday, February 22, 2011 10:16 PM

All replies

  • if you are using a domain or machine account then:

    Integrated Security=SSPI;User ID=DomainOrMachineName\UserName";

    else if SQL Server account:

    Persist Security Info=True;User ID=UserName;Password=UserPassword";

    Tuesday, February 22, 2011 5:33 PM
  • Which exact database do you have? Check here for the correct conn. string.

    Mitja

    Tuesday, February 22, 2011 5:43 PM
  • I have tried both.  None have worked.

     

    And to answer the second question, it is SQL Server 2008.  I have a console application that uses a connect string that works perfectly but this web service will not.

     

    Let me know if I need to post the full code.

    Tuesday, February 22, 2011 7:23 PM
  • Try to chang the order to:

    Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
    

    This has got to work... if your server is running and all data (ip, database name, userName nad pw) are correct this must  work.

    Be careful when you name the database - it has to be only the name, without any dot and ending word (so only "DATABASE NAME").

    Tuesday, February 22, 2011 7:37 PM
  • so you are running this as a web service...

    do you have UDDI installed on the server running this service?

    is this service external access? if so... is your IIS configured for external access?

    Tuesday, February 22, 2011 8:17 PM
  • myConnection = new SqlConnection("Initial Catalog=myDB;Data Source=IPofServer;User ID=LocalUserwithDBOaccess;Password=pw;"); 

    2011-02-22 09:42:00.53 Logon       Error: 18456, Severity: 14, State: 38.
    2011-02-22 09:42:00.53 Logon       Login failed for user 'Domain\UserName'. Reason: Failed to open the explicitly specified database. [CLIENT: IP]

     

    The domain account that it is listing is my account which also has DBO access to the database.  The two funny things is, my account has access and it is not even trying to use the SQL account that I set up in the string.  What would cause this issue or do I have something wrong with my connection string?

     

    From looking at the error message's perspective, It is sure that your connection string is CORRECT, the second thing to check is whether the specified userid is the database's user, in other words the specified user id may be associated with the different database and you are trying to open a different database, please check this.

     


    Balaji Baskar
    http://codesupport.wordpress.com
    Click on "Vote As Helpful" and "Mark As Answer" if this has helped you.
    Tuesday, February 22, 2011 8:28 PM
  • Thank you for all of the responses.  I will answer them in order.

     

    Changing the order for the connection string made no difference.

     

    No external access.

     

    That user is only associated with one database and it is set up as DBO.

     

    I bet this is something very simple, I just can't figure it out.

    Tuesday, February 22, 2011 8:46 PM
  • okay, so i should have forgotted to read something important here, I just noted that this is in your webservice? Remember IIS handles security in a different way:

    Few things to note here:

    For ASP.NET 2.0 applications, you should store connection strings in the <connectionStrings> section of your application's Web.config file. The connection string used with Windows authentication must include either the Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI, as shown here.

    <connectionStrings>
     <add name="MyDbConn1" 
        connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
     <add name="MyDbConn2" 
       connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
    </connectionStrings> 
    
    Next there are two primary things:
    • Using the Network Service Account
    • Using a Custom Service Account
    Refer: http://msdn.microsoft.com/en-us/library/ff647396.aspx

    Balaji Baskar
    http://codesupport.wordpress.com
    Click on "Vote As Helpful" and "Mark As Answer" if this has helped you.
    • Proposed as answer by Balaji Baskar Wednesday, February 23, 2011 2:59 PM
    • Marked as answer by Matt11380 Wednesday, February 23, 2011 3:19 PM
    Tuesday, February 22, 2011 10:16 PM
  • Ah crap.  I knew it was something simple...

     

    Thank you

    Wednesday, February 23, 2011 2:12 PM