Share via

Linked Server Login Limitation

Ray Milhon 21 Reputation points
2021-07-05T14:26:43.697+00:00

At our company we have several SQL Server Databases. one is at our data center one is local at our site and one is at Corporate HQ. The local SQL Server is where we run most of our reports from and we use that to link to the Data Center and Corporate HQ databases. The issue is that the Corporate uses Windows Authentication and the linked server requires SQL Server Authentication is there anyway around this limitation?

Also in asking this question there's a section below that says Tags. It's a required field but doesn't allow me to put the appropriate tag. There's a list but this issue isn't in the list and I don't have permission to create one. So I picked one at random so this question could be asked.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,826 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 111.5K Reputation points MVP
    2021-07-05T22:03:11.9+00:00

    Linked servers can be set up with self-mapping or with mapping to a proxy login. Self-mapping means that if DOMAIN\SUE logs into the local server and runs a query on the remote server, she will log on to the remote server as DOMAIN\SUE and have the same permissions as she had logged in directly on that server. This is the preferred model.

    With a proxy login DOMAIN\SUE will log on to the remote server as that proxy login, and have the permissions of that proxy login. If the queries can be confined to an application, this may be OK, but else there could be a security problem, since SUE gets access to data, she does not have permission to.

    In any case, this proxy login must on SQL login on the remote server. It cannot be a Windows login.

    0 comments
  2. AmeliaGu-MSFT 13,976 Reputation points Microsoft Vendor
    2021-07-06T03:10:57.53+00:00

    Hi RayMilhon-1409,
    Welcome to Microsoft Q&A。

    The issue is that the Corporate uses Windows Authentication and the linked server requires SQL Server Authentication is there anyway around this limitation?

    We can use SQL Server Authentication login in the linked server.

    To set how a user would authenticate to the remote instance, please go to Server Objects-> right-click Linked Servers-> New Linked Server dialog, select the Security page in the SSMS:

    111938-image.png

    Local login: The local login can be either a login using SQL Server Authentication or a Windows Authentication login. Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login.

    Impersonate: The Impersonate check box when is checked passes the local login credentials to the linked server. For SQL Server Authentication, the same login with the exact credentials must exist on the linked server. To use impersonation, the configuration must meet the requirement for delegation.

    Remote User: Use the remote user to map users not defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

    Remote Password: Specify the password of the Remote User.

    Be made using the login’s current security context: If this option is chosen, it will pass the current security context of the local login to the remote login. If SQL Server Authentication is used, then the local login credentials will be passed to remote SQL Server. Note, to establish connection to remote server successfully, then the user with the exact same credentials must exist on the remote server.

    Be made without using a security context: Specify that a connection will be made without using a security context for logins not defined in the list.

    Please refer to this doc and this article for more details.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  3. Tom Phillips 17,731 Reputation points
    2021-07-06T13:00:54.953+00:00

    Your login is almost certainly setup as SQL login due to the double hop issue with AD logins.

    Please see:
    https://www.mssqltips.com/sqlservertip/2312/understanding-when-sql-server-kerberos-delegation-is-needed/

    0 comments
  4. Ray Milhon 21 Reputation points
    2021-07-08T13:22:21.98+00:00

    unfortunately no, It's still not working. Thanks for trying

  5. Ray Milhon 21 Reputation points
    2021-07-08T15:25:07.557+00:00

    The corporate sql server uses windows authentication. When I try to login through a linked server I get this error message. Doesn't seem to matter what options I set in the linked server connection this is what I get.

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?

    ------------------------------

    ADDITIONAL INFORMATION:

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

    ------------------------------

    Login failed for user 'HPN\RMILHON'. (Microsoft SQL Server, Error: 18456)

    For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

    ------------------------------

    BUTTONS:

    &Yes
    &No

    ------------------------------

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.