0

I'm trying to setup a SQL Server 2008 R2 instance, restricting access to my database so only specific users on specific machines can login there.

Let's say, we have 3 devs: John, Jill and Steve, with 3 machines: JohnPC, JillPC and StevePC. John and Jill use their machines inside a domain, but Steve doesn't. So, the machines "full names" are: MYDOMAIN\JohnPC, MYDOMAIN\JillPC and StevePC, since Steve isn't in MYDOMAIN, but in windows default "WorkGroup".

I know that I can create accounts to restrict access from a machine, as in [MYDOMAIN\JohnPC$].

But in my case, I don't want to allow Jill to login from JohnPC.

I'm using mixed-mode login to server, so I have the 'sa' account enabled (and it's supposed to access only within the server's localhost environment).

I've read this article that shows how to create an account for a computer within a domain, but that doesn't apply to my case [Edit: because it doesn't tell how to restrict user to each computer, and it doesn't tell how to create an account for a computer with no domain]. Searched google about it, and can't find any answer that could remotely fit what I'm trying to do.

So, is this possible to achieve? And if it is, how can I do it?

Improve this question
1
  • 1
    What is the purpose of the restriction? If your data is properly protected within the database and your users are properly authenticated by the domain and/or the database it shouldn't matter precisely which client your user connects from, or even with which client software. You are setting yourself up for a maintenance nightmare every time a user gets a new workstation or laptop and they need access to the database (which they should already have), but now they have to wait for you to grant access to the new hardware.
    – pmdba
    Apr 23, 2020 at 2:38

1 Answer 1

Reset to default
1

You can create a server level trigger for LOGON and in there you can check the user, domain and the client host name using sys.dm_exec_sessions to determine whether user is allowed or not. (We can have the permitted combinations in a separate table if require)

I have done a similar thing to stop users accessing the SQL Server databases using SSMS with any SQL Logins. Only domain users are allowed via SSMS.

Hope this helps you.

Improve this answer
4
  • I saw this suggestion about using logon trigger elsewhere, should've mentioned it in original post. Someone has warned about using this could cause performance problems.
    – Youkko
    Apr 23, 2020 at 13:23
  • 1
    I would second the use of a login trigger. I have used them in the past to prevent application developers from using the application service account to connect to a database from anything other than the app servers/web servers. This really only worked well because the servers are configured with static IP addresses. Doing the same for workstations (that often have dynamically allocated IP addresses) could be a maintenance nightmare though.
    – Patrick
    Apr 23, 2020 at 14:01
  • 1
    Another issue is that many of the things you would filter against - IP addresses, application names, etc. - can be fairly easily spoofed by a malicious client. The trigger approach is not a replacement for proper in-database, row-level security.
    – pmdba
    Apr 24, 2020 at 11:40
  • I dropped this idea. It could really cause a maintenance nightmare. But I'll mark this one as the answer - that's a valid answer anyway. Thank you all, guys. You were awesome!
    – Youkko
    Sep 11, 2020 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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