2

I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone.

Here is the topo:

I have private software that I'll call X installed on a Hyper-V virtual server. Users of this software connect through virtual machines using the Remote desktop.

The application has its own SQL Server database instance. The security for this database is Windows Authenticated. Application X users all belong to a user group in the Windows Server 2019 Active Directory (AD).

On the other hand, I have a Y application (Microsoft Dynamic 365) that is installed locally on each workstation and which has its own SQL instance. The application uses an SQL database which I would also call Y.

As I would like to share information between the 2 SQL instances, I created a linked server on the X instance to the Y instance. I succeeded with some SQL query to insert, update, and delete some information in a database of instance Y from my instance X. To do this, I granted myself DB_owner rights on the database of X.

I have reached the stage of allowing user group X to be able to run from application X using the same queries that interact on SQL server Y.

However, I realize that I need to create each user of X in the SQL instance of application Y from the Security, Connection menu. My requests work when I grant DB_Owner roles. I would have thought that I could have used the same group used in AD for my X application. I tried well but it doesn't work.

I don't know if this is due to the configuration of my linked server.

Here is the configuration I am using:

enter image description here

enter image description here

Could someone point me to a solution to avoid having to recreate every user of my AD from X in my instance Y and just link my group X.

1

With groups it should be work in the same way as single user. You must have in place proper spn to grant kerberos delegation between the two instances.

Maybe you have tried from ssms so you haven't done the two hops for authentication. In that way NTLM is granting you permission but when you are using an application you will need to grant authentication on two hops and so you fall on the anonymous authentication error that is not granting your user proper login.

Please check it.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15

1
  • Thank you for your reply. To be honest, initially I had to look for a way to link my 2 SQL instances and I found this link which was very useful to me: sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos I followed the steps in this link, ran some tests and it works. Also, I didn't have to select the last step offered in this link: the Trust this user for delegation to any service (Kerberos only) option because I didn't have the Delegation tab in the AD.
    – JF Landry
    Apr 29 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.