locked
"Login Failed for Use" error with Linked Server RRS feed

  • Question

  • Hi

    I have 2 linked servers that link back to a common server. I have one app at this time that uses the linked server to retrieve data from them. I have a few questions in regards to the above error message.

    1. When the linked server was installed I believe that the domain/Administrator was set as dbo. Can this be changed? Reasoning, If I use the app under the administrator account, I can't connect. I get the "Login Failed for User" error. Investigating the error, I noticed that the admin account was not linked to a DB. When I tried to add the admin account to the list of users for the database, I would get the following error:

    Create failed for User 'Domain\Administrator'.  (Microsoft.SqlServer.Smo)

    -----------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)

     

    2. Is there a way to setup a common user profile that anyone using my app could be assigned that would not require me to add the app user to the remote servers? Would a Service Principal Name help provide that feature? Ideally, I'd like the user to login to there machine as usual, but anytime they make a call to the remote db through my app, I'd like the system to use the User Profile to allow them through. I'm hoping to avoid the need to add new users to all three machine every time I have a new user.

    Thanks for any help.


    Tuesday, January 4, 2011 2:39 PM

Answers

  • Hi Mikelll,

    1. When the linked server was installed I believe that the domain/Administrator was set as dbo. Can this be changed? Reasoning, If I use the app under the administrator account, I can't connect. I get the "Login Failed for User" error. Investigating the error, I noticed that the admin account was not linked to a DB. When I tried to add the admin account to the list of users for the database, I would get the following error:

     When you create this linked server, how do you setup security (server login mappings)? Please make sure well configure this.

    2. Is there a way to setup a common user profile that anyone using my app could be assigned that would not require me to add the app user to the remote servers? Would a Service Principal Name help provide that feature? Ideally, I'd like the user to login to there machine as usual, but anytime they make a call to the remote db through my app, I'd like the system to use the User Profile to allow them through. I'm hoping to avoid the need to add new users to all three machine every time I have a new user.

    Yes, you can reate a domain group as a login and add all those domain users that need to access to this instance to that domain group. And when you create the linked server, map this loging (domain group) to a remote server login or using the login's current security context if you also create a login from that domain group on the remote server.

    In reference to question 2. Could Ownership Chaining help in allowing me not to have to create a user on all the SQL servers?
    Thanks

     Ownership chain only work within a single SQL Server instance but not cross SQL Server instances.

    Please let me know if you need more help.

    Thanks,

    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, January 6, 2011 5:59 AM

All replies

  • In reference to question 2. Could Ownership Chaining help in allowing me not to have to create a user on all the SQL servers?
    Thanks
    Tuesday, January 4, 2011 6:32 PM
  • Hi Mikelll,

    1. When the linked server was installed I believe that the domain/Administrator was set as dbo. Can this be changed? Reasoning, If I use the app under the administrator account, I can't connect. I get the "Login Failed for User" error. Investigating the error, I noticed that the admin account was not linked to a DB. When I tried to add the admin account to the list of users for the database, I would get the following error:

     When you create this linked server, how do you setup security (server login mappings)? Please make sure well configure this.

    2. Is there a way to setup a common user profile that anyone using my app could be assigned that would not require me to add the app user to the remote servers? Would a Service Principal Name help provide that feature? Ideally, I'd like the user to login to there machine as usual, but anytime they make a call to the remote db through my app, I'd like the system to use the User Profile to allow them through. I'm hoping to avoid the need to add new users to all three machine every time I have a new user.

    Yes, you can reate a domain group as a login and add all those domain users that need to access to this instance to that domain group. And when you create the linked server, map this loging (domain group) to a remote server login or using the login's current security context if you also create a login from that domain group on the remote server.

    In reference to question 2. Could Ownership Chaining help in allowing me not to have to create a user on all the SQL servers?
    Thanks

     Ownership chain only work within a single SQL Server instance but not cross SQL Server instances.

    Please let me know if you need more help.

    Thanks,

    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, January 6, 2011 5:59 AM
  • Hi Chunsong,

    Thanks for the reply. I have all the servers setup with "Sql Server and Windows Auth".

    Michael

    Thursday, January 6, 2011 3:23 PM
  • Hi Michael,

    Thanks for your update. Is you problem resolved?  If not, please provide more information as I said in my last reply.


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, January 7, 2011 3:07 AM