blocking folks from using a linked server and plumbing one to specific dsn

Question

Post reply

blocking folks from using a linked server and plumbing one to specific dsn

stan

Ten Centuries

Points: 1189
More actions
February 13, 2025 at 8:36 pm
Go to Answer
#4537032

hi, as we get further and further into netsuite connectivity, we want to block just about everyone (dbas, myself etc etc) from using a certain linked server that is plumbed to a production accounting instance of netsuite. Presumably plumbed to a dsn set up with the creds to see data there.
I think i have half the question answered (the deny) at this link https://stackoverflow.com/questions/3136015/sql-server-how-to-deny-users-access-to-linked-servers and also shown below.
but what can we do to stop myself, a dba etc from creating another linked server that plumbs to the forbidden dsn? so far im finding nothing. i will post the answer here if i stumble on it.
frederico_fonseca

SSCoach

Points: 16250
More actions
February 17, 2025 at 9:28 am
Answer
#4539450

issue with using a "specific login" is that a sysadmin can execute as any login - so it will still not prevent a DBA from doing it which was the original issue.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80356 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Here's the section of the documentation that seems to apply here and it's in the form of step-by-step instructions. I do NOT know for sure if this will prevent DBAs that have SYSADMIN privs from being able to see things on the remote computer only because I haven't tried, but the documentation here seems much more restrictive than what most people are even aware of.

https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16#edit-the-security-page-for-the-linked-server-properties

I think that the key will be in how you want to handle unmapped logins. You'll want to reject those.

If that doesn't work, then you'll have to go with what Frederico said over on your original post on that subject and that would be "Not Possible" to prevent DBAs with sysadmin privs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stan Ten Centuries Points: 1189 More actions · Answer 3

thx jeff and frederico. if the odbc driver's vendor comes up with something i'll post it here.

the good news is that i showed my boss the potential breach and he's fine with what we have right now.

i have no idea why this post is showing twice on sql server central.

Johan Bijnens SSC Guru Points: 135550 More actions · Answer 4

LOL, they don't want their MD to see what illness they have but want to have a treatment for a cure

If you don't trust your DBA, outsource the system and pay the price!

( However, it's always a nice excercise to check evolution on linked servers )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me