October 31, 2014 at 10:11 am
Hello. I have looked all over boards and can't seem to find any elegant (or even hacky) workaround for this situation, hoping some of you geniuses can help. SQL Server 2008 R2 Enterprise.
We have a linked server to a DB2 server. The current linked server uses an DB2 (Unix) ID/PWD (batchID/service account) to the linked server. We don't have Kerberos and the mixed bag of Windows/Unix would sort of disallow it anyway. The SQL server (while mixed authentication mode) uses almost exclusively Windows Authentication and we are very good about using AD groups for everything, not INDIVIDUAL windows/sql logins (as part of our corporate security).
The data on the DB2 server will soon have masked confidential fields for individual users but not the batch IDs. To ensure that anyone connecting to our SQL server can't just pull up the the unmasked data, we thought we would create another linked server an limit it to a specific Windows group - lets call it 'DOMAIN\DBA', that requires unmasked data for processing. Either that or -better- put the 'DOMAIN\DBA' group to the existing linked server mapping to an "unmasked" account on the DB2 side. This group has sysadmin and consists of a windows service account and prod support staff who have to manually run processes). But it is IMPOSSIBLE to set Windows Group LOGIN permissions on a linked server login. The sp_addlinkedsrvlogin specifically excludes server_principals type 'G'. Microsoft will not say why, but there you go.
Does anyone know a way to do this without having to add the individual users from the 'DOMAIN\DBA' group to individual logins on the server? That just seems like a maintenance nightmare. This affects about 20 SQL servers in our organization. PLEASE? Anyone?
Thanks.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply