4

Using Windows authentication, is there a way to prohibit users from connecting via ODBC to the database?

Improve this question
2
  • Do you want to restrict ODBC connection to a certain database or the entire SQL instance?
    – Scott Hodgin - Retired
    Oct 23, 2019 at 16:07
  • both solution can work
    – Xilmiki
    Oct 23, 2019 at 16:19

2 Answers 2

Reset to default
8

The feature designed to help in this scenario is Application Roles. With an Application Role you can allow the user to connect to the target database, but not be able to run any queries until the application supplies a secret to activate the Application Role.

Improve this answer
4
  • 1
    I can never decide if this is SQL Server's worst marketed or best kept secret feature. LOL!
    – John Eisbrener
    Oct 23, 2019 at 20:21
  • 1
    It’s not perfect, At least for desktop applications, as a user with a debugger can discover the Application Role secret.
    – David Browne - Microsoft
    Oct 23, 2019 at 20:31
  • I have been using sql for several years. Incredibly I had never taken into consideration this feature. Thank you!
    – Xilmiki
    Oct 26, 2019 at 8:00
  • "as a user with a debugger can discover the Application Role secret"-> to solve this problem i'm planning to set app role in custom stored procedure, based on current user, at application startup so only dba can view the definition.
    – Xilmiki
    Oct 26, 2019 at 8:11
7

A possible solution is to create a server logon trigger that would check the app_name(). I created the following server trigger on my local instance and tried to connect via ODBC. The connection was rejected. You would have to modify for your needs.

CREATE TRIGGER trgGetAppName ON ALL SERVER
FOR LOGON AS

IF APP_NAME() NOT LIKE ('Microsoft SQL Server Management Studio%')
    AND APP_NAME() NOT LIKE ('.Net SqlClient Data Provider%')
BEGIN
    ROLLBACK
END

A word of caution about logon triggers:

A logon trigger can effectively prevent successful connections to the Database Engine for all users, including members of the sysadmin fixed server role. When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database Engine in minimal configuration mode (-f)

Improve this answer
3
  • 1
    Logon triggers can be bypassed with the proper effort, but perhaps a login trigger in conjunction with a lookup against the sys.dm_exec_connections (specifically looking at the client_interface_name column for ODBC connections) would do the trick for most scenarios.
    – John Eisbrener
    Oct 23, 2019 at 19:59
  • @JohnEisbrener - Thanks for the comments!
    – Scott Hodgin - Retired
    Oct 23, 2019 at 20:09
  • 1
    You are aware that the app name can be modified client-side right? This is absolutely not secure.
    – Voo
    Oct 24, 2019 at 10:27

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.