none
SqlConnection String issue RRS feed

  • Question

  •  


    Hey,

    We have a windows service connects to a sql server 2005. we got following error message

    Login failed for user ''. The user is not associated with a trusted SQL Server connection.

    After review code, we know the integratedsecurity=true was missing in connectionstring. We understand modify code will fix
    this problem but we are looking for an alternative solution fixing this issue without modify code. After a little research,
    we know adding trusted relationship between the machine hosting windiws service and SQL server 2005 can fix this issue.
    Do we have another way to fix this?

    Also, we have DataSource and Initial catelog in configuration files. SqlConnectionStringBuilder build up connectionstring based on value in those values. Does there have a way to put IntegratedSecurity=True into the value of host or name. So we can have a connectionstring with value of IntegratedSecurity.

       <add key="MainDatabaseHost" value="xxxx" />
       <add key="MainDatabaseName" value="yyyyyyyy" />


    Thanks,

     

    Monday, December 8, 2008 2:33 PM

All replies

  • All you need to do is to store the whole connection string in ConnectionStrings configuration section of your .config file. In this case you do not need to change any code. It is preferable way to store connection strings that allows you to change them any time without application modification and provides good level of maintainability. I do not see why you would need to store separate parts of connection string and then build it internally in your code.

    Tuesday, December 9, 2008 11:25 AM
    Moderator
  • Thanks your post. I know that is the best practice. However, we have a legacy code and our manager instist put connection string builder in code. Even we can rebuild code to fix this problem, but we would like find reason why it was working in some environment. Based on my knowledge, that should not work. I guess there have some configuration change in SQL server side...

     

    Wednesday, December 10, 2008 3:46 PM
  • Check if Windows service was not switch to run under different Windows account that has no access to SQL Server. Keep in mind that if service hosted under local account, it will not have an access to the network.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, December 11, 2008 11:06 AM
    Moderator
  • The NT account service used has permission to access SQL Server. We have four different SQL server. The windows service can connect to three of them and execute store procedure. However, it throw login in exception for one SQL server. Both sql server use Mix Authen. mothod.

    That is weired problem and we have no idea why this was working in three SQL Servers.
    Tuesday, December 16, 2008 8:18 PM
  • If it is same server that has an issue, I would check server's configuration. Make sure that it is configured to support  integrated security. Another potential issue is if this server is behind firewall, all the calls could be blocked to it.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, December 18, 2008 10:53 AM
    Moderator