question

RayMilhon-1409 avatar image
0 Votes"
RayMilhon-1409 asked AmeliaGu-msft answered

Linked Server Login Limitation

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi RayMilhon-1409,

How are things going on?
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.




image.png (31.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RayMilhon-1409 avatar image
0 Votes"
RayMilhon-1409 answered RayMilhon-1409 commented

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

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is your current setting "For login not defined above"? Do you have any login mappings defined?

What exactly is the error message you are receiving?

0 Votes 0 ·

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

Exactly what is not working?

I detailed in my first post what you can do with linked servers - and what you cannot. If the remote server only uses Windows authentication, the only option is self-mapping. You can't use a Windows account as a proxy account.




0 Votes 0 ·

Not exactly sure what you mean by self mapping

0 Votes 0 ·

I did explain that in my original post. Self-mapping is the default - that's what you get if you only create the linked server.

0 Votes 0 ·
Show more comments
RayMilhon-1409 avatar image
0 Votes"
RayMilhon-1409 answered

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://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error


BUTTONS:

&Yes
&No


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi RayMilhon-1409,

Could you please share us the security tab in the linked server properties?
If you are using windows authentication in the remote server, you have to select Be made using the login's current security context to connect to the remote server using the same Windows Authentication credentials in the local server. Please refer to this similar thread which might help.

Best Regards,
Amelia


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.