The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

How to prevent users from accessing SQL Server from any application or any login expect your main application & its login

Posted by Sudarshan Narasimhan on April 7, 2012


I heard of these interesting requirements when working with one of my customers. If I were to break it down, there are 2 types of scenarios where we want to restrict access to SQL Server.

  1. Only the main application which uses a SQL Login should be able to connect to the SQL Server. None of the Windows Logins or any other login should be able to connect the SQL database. In order words, the SQL database should be accessed only by the Application Login and no one else.
  2. Access to SQL Server and the database should be only through a custom application and no other application including SQL Server Management Studio, SQLCMD etc. should allow users to access the database.

Solution for Scenario #1

The easiest and secure way of preventing other logins from accessing SQL Server is through Logon Triggers. You might wonder why this is an issue, when only required logins are added in SQL Server. Think of a scenario where the database is deployed on client machines and obviously the client machine means the user will be an Administrator on his own machine. A perfect example of this is SQL Server Express databases.

You can use Logon Triggers to prevent Windows logins or other logins from accessing SQL Server. Logon triggers are fired every time a new connection is established to SQL Server. Just like regular triggers we can perform a ROLLBACK which will roll back the connection if the login is not your application login.

For this example assume that your application has a login called “MyApplicationUser” and this is the only login that should have access (even the local Administrator must not be able to access SQL Server).

Here is the T-SQL code to implement such a requirement

--Step1: Create Audit Table
USE master
GO
CREATE TABLE dbo.loginAuditTable (
    id INT IDENTITY PRIMARY KEY,
    data XML,
    program_name nvarchar(128)
)
GO

--Step2: Create VIEW To read XML Audit Data in user-friendly format
use master
go
CREATE VIEW dbo.loginAuditView
AS
SELECT id
      ,data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname') AS EventType
      ,data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS PostTime
      ,data.value('(/EVENT_INSTANCE/SPID)[1]', 'int') AS SPID
      ,data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(257)') AS ServerName
      ,data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS LoginName
      ,data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname') AS LoginType
      ,data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname') AS ClientHostName
      ,data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit') AS IsPooled
      ,program_name
      ,data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(85)') AS SID
FROM master.dbo.loginAuditTable
GO

--Step3: Create Logon Trigger to block all users expect SA & MyApplicationUser
IF EXISTS(
    SELECT * FROM master.sys.server_triggers
    WHERE parent_class_desc = 'SERVER' AND name = N'Allow_only_Application_Login_Trigger')
DROP TRIGGER [Allow_only_Application_Login_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Allow_only_Application_Login_Trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()
 
DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType sysname
       ,@LoginDomain sysname
       ,@HostName sysname
SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
      --,@LoginDomain = @data.value('(/EVENT_INSTANCE/LoginDomain)[1]', 'sysname')
      ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
      ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

IF @LoginName not in ('MyApplicationUser','sa') 
    BEGIN
        ROLLBACK; --Disconnect the session
        --Log the exception to our Auditing table
        INSERT INTO master.dbo.loginAuditTable(data, program_name)
        VALUES(@data, @AppName)
    END 
END;

 

When a user who isn’t in the logon trigger exception list above tries to connect, they will get this error.

TITLE: Connect to Database Engine

——————————

Cannot connect to SERVERNAME.

——————————

ADDITIONAL INFORMATION:

Logon failed for login ‘Domain\TestUser1’ due to trigger execution.

Changed database context to ‘master’.

Changed language setting to us_english.

(Microsoft SQL Server, Error: 17892)

Every time a user who isn’t SA or MyApplicationUser tries to connect to SQL Server and failed with error given above, you will see the following information in the audit table including the login name, client machine name, application name etc.

image


Note:

Be careful when creating logon triggers as any mistake/bug in your code logic, will block you and then you wont be able to connect back in again. If you are in such a situation, then you can use the Dedicated Admin Connection (DAC) to make a connection and then disable/drop the logon trigger. Connections via the DAC don’t fire login triggers and there can be at max 1 DAC connection for an entire instance. DAC can enabled from sp_configure.

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;

As a best practice, make sure you have DAC enabled on all your SQL instances, because it is a life-saver in situations like this Smile. To make a DAC connection use the -A switch, sqlcmd -A -D master or Admin:ServerName from SSMS.

Solution for Scenario #2

Scenario #2 is a little more interesting and more complicated. Basically, the ask is to prevent all database access except from 1 specific client application. So even tools like SSMS, SQLCMD, Profiler etc. should not be able to connect to SQL Server.

We can still implement a reasonable amount of security using Logon Triggers, but it is not 100% fool-proof. With the same logon trigger code given above, we need to use the program_name from sys.dm_exec_sessions to check if this program is allowed to establish a connection to SQL Server.

--Step3: Create Logon Trigger to block all users expect SA & MyApplicationUser
IF EXISTS(
    SELECT * FROM master.sys.server_triggers
    WHERE parent_class_desc = 'SERVER' AND name = N'Allow_only_Application_Login_Trigger')
DROP TRIGGER [Allow_only_Application_Login_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Allow_only_Application_Login_Trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()
 
DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType sysname
       ,@HostName sysname

SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
      ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
      ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

IF @AppName not in ('MyProductionApplicationName1', 'MyReportingApplicationName2', 'Microsoft SQL Server Management Studio', 'Microsoft SQL Server Management Studio - Query') 
    BEGIN
        ROLLBACK; --Disconnect the session
        
        --Log the exception to our Auditing table
        INSERT INTO master.dbo.loginAuditTable(data, program_name)
        VALUES(@data, @AppName)
    END 
END;

 

This is the output from the Auditing table to track the applications and users who tried to connect to SQL Server.

image


The catch with this method is the the program_name or application name is not really a fool-proof method, since it is very much possible for someone to provide the correct application name in the connection string and get connected to SQL Server. There is a property called ApplicationName which can be set in the connection string. So if the application name is known, anyone can write a program to provide that name in the connection string and gain access to SQL Server.



E.g.

OLEDB Connection String

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=ProdServer1;Application Name=MyProductionApplicationName1

SQL Native Client Connection String

Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=master;Data Source=ProdServer1;Application Name=MyReportingApplicationName2

If we use the [Application Name] property in the connection string, then even if we have logon triggers, it will allow the user to connect to SQL Server, since we are just matching the application name which is specified in the connection string.

A more secure approach you could use for this is called "Application Roles". When connecting from an application you assume a particular role and only that role is granted privileges required in the database. So all apps connect via this mechanism and don’t give out SQL or NT logins for any unauthorised use. You will have to call sp_setapprole in your application with a password, once the connection is established. Once this call succeeds then the connection gets the privileges of the application role and loses privileges of the actual user, which is what we want. So if someone tried to connect to the database from SSMS or SQLCMD, they will access the DB using their credentials, which won’t have the required permissions on the tables, since only the application role has the rights on the tables. This is more secure & reliable approach, but one that requires application code change and as a DBA you will have to create the application role in SQL Server.

The steps to create application roles are given in Books Online.

Advertisements

12 Responses to “How to prevent users from accessing SQL Server from any application or any login expect your main application & its login”

  1. Mariusz said

    Very nice solution. I was looking for somethink like that. Greta. Thanks.

  2. Aamir said

    Thanks, very interesting

  3. George said

    Thank you very much for the solution.

  4. stefano said

    Is there a way to implement a logon trigger to enable the connection from only the application server for specific users ?
    The scenario is to prevent users to connect directly to the DB server using their “application” credentials from their PC .
    We wish not to use firewall rules
    thanks

    • Yes, you can use the @HostName variable which stores the IP Address of the host to implement this. In the logon trigger you could modify the filter condition like
      IF @HostName ‘1.2.3.4’)
      BEGIN
      ROLLBACK;
      END

      This will block all connections expect from the particular application server. Be careful and test this before putting it on Prod 🙂

      TheSQLDude

  5. Ray said

    Is there a way to specify only doing this for a specific database connection? I need to implement for one specific database not across the entire system. I was looking at joining to sys.dm_exec_requests, but I don’t believe it is what I want.

    • Matt Jaques said

      Also looking for this as well for one database only

      • Keep in mind, LOGON server triggers come with a cost (albeit small) as they get “Triggered” for every logon request coming in to the SQL server instance. Wouldn’t it be better to implement security roles/hardening to NOT grant access to users to this database instead? Or revoke access to the groups/users who shouldn’t be accessing the DB.

        Can you share more details about your scenario, i.e. why do you want to prevent access to a specific database via a server logon trigger? I can help, once I can understand what you’re trying to achieve.

  6. easyoradba said

    Reblogged this on SimpleSQLDBA | Shadab Mohammad.

  7. For the first two steps you used master database “use master”
    But for third you did not use that, so we have to run first two steps under master database and third under our database? Is that right?

    • The 3rd step can be under any database context (as long as the user running it is sysadmin). It doesn’t make any difference since Server Logon Triggers reside in master database.
      If you notice the third step, under the EXISTS condition I am explicitly querying master.sys.server_triggers using three part naming format.

  8. khanafzal said

    I wan to view the sa login details where user connected the SQL instance and login with sa, I need to view [EventType],[PostTime],[ServerName] ,[LoginName] ,[LoginType] ,[ClientHostName], [Ipaddress],[databasename],[tablename]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: