Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a MySQL server that I need to set up as a linked server in SQL Server 2014.
I need a login from the SQL Server (which is a Windows group) to impersonate a username at the MySQL end.
I can see from Googling that this cannot be done.
I can create a new Windows login for every user in the Windows Group and map them, but was wondering if there is a slicker way of doing this?
I am not an admin of the MySQL server so cannot make any config changes there.
Further reading here (no affiliation): https://searchsqlserver.techtarget.com/tip/Managing-linked-server-security-in-SQL-Server
A linked server offers a few methods for connecting to the remote system. Since MySQL as a remote system does not understand windows authentication or participate in Kerberos you are limited to the following:
Login mapping - as you said, each user in the windows group would need to be added to the linked server security tab, with a login on the MySQL side. There is no reason that they cannot "share" the same login on the MySQL side, but each user would need to be added to the linked server on the SQL Side.
Be Made Using this security context - You can provide a single username and password here, such that every login on SQL Server would use it when using the Linked Server.
The only one that offers fine grained security control is the Login Mapping method. Otherwise, any user who has access to SQL would have the access to MySQL that is granted by the Linked Server.
