Click here to Skip to main content
15,905,785 members
Articles / Database Development / SQL Server

Restricting Logon to SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
18 Oct 2016CPOL15 min read 18.5K   398   12  
Discussion and a few approaches to restrict logging into SQL Server using a logon trigger or into a database by using a specific procedure

Introduction

Sometimes, you may need to restrict the user from logging into SQL Server. This can be done in several ways and this article discusses two main techniques:

  • Using a logon trigger to prevent logging into SQL Server
  • Using a database specific procedure to control login into the database

The variations using logon trigger are typically used when the user authentication is done by the SQL Server. The procedural approach can be used in many kinds of situations, even if the connecting login account is always the same from SQL Server point of view. Of course, these examples can be modified in several ways to achieve the desired logic.

The topics discussed are:

First Things First, A Few Warnings

We’re going to play with logon triggers so there’s always a risk that the trigger will not perform as expected. In such case, the trigger may prevent you completely from logging into the SQL Server. In this kind of situation, you need a Dedicated Admin Connect (DAC) in order to be able to connect to SQL Server and to modify or drop the trigger. To establish a DAC, see How to Establish Dedicated Admin Connection (DAC) to SQL Server.

Another thing is that preventing the logon should not be confused with authentication. The methods discussed in this article only take additional steps for allowing or preventing to continue with the already successful logon. If you want to know more about handling authentication, please refer to Authentication in SQL Server.

The third thing is that the examples in this article partially rely on the information provided by the client application. This also means that the information may not be reliable so the techniques work typically only for the applications you have control over. In other words, when you want to control in what situations your application may or may not connect to your database, these examples may come in handy.

The Test Program and the Test Database

In order to do some testing, I prepared a small WPF program. The idea is that you can start two of these applications and try different connection variations in different kinds of situations. What comes to the actual logic, the type of the client doesn't matter. Client technology could well be ASP.NET, Windows Forms or even a console application.

Image 1

Before we start, we also need the database and a few logins. These can be created as follows:

SQL
-----------------------------------------------------------
-- Create the test database and users
-----------------------------------------------------------
USE master;

CREATE DATABASE LogonTest

CREATE LOGIN LogOnUser WITH PASSWORD = 'DontForgetThis1'; 
GO;

-- Grant access to see sys.dm_exec_sessions
GRANT VIEW SERVER STATE TO LogOnUser; 
GO;

CREATE LOGIN NormalUser WITH PASSWORD = 'RememberMe2'; 
GO;

USE LogonTest;
GO;

CREATE USER NormalUser FOR LOGIN NormalUser;
GO;

The first login, LogOnUser is only used to execute the logon trigger so it has no other function. The other one, NormalUser is used for actually connecting to the database, just like a normal user would. This is why their privileges differ from each other:

  • LogOnUser has VIEW SERVER STATE privilege in order to be able to query sys.dm_exec_sessions
  • NormalUser is added as a user to our test database in order to be able to connect to it

Using a Logon Trigger to Allow Only One Connection Per User from an Application

Now let’s try the first variation. If we need to allow only a single simultaneous connection per user from our test program, the trigger could look like this:

SQL
-----------------------------------------------------------
-- Create a logon trigger to allow only one connection
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
   @connectioncount int;
BEGIN 
   -- Select the amount of connections for the user from an application
   SELECT @connectioncount = COUNT(*)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process = 1
   AND   lower(a.program_name) = 'login tester'
   AND    a.original_login_name = ORIGINAL_LOGIN();
 
   IF  (@connectioncount > 1) BEGIN
      ROLLBACK;  -- rolling back causes the connection to fail
   END;
END;
GO

Few things to notice: The trigger itself is a special trigger which is created ON ALL SERVER …. FOR LOGON. This means that the trigger is server scoped and reacting for logon action. Also, the trigger is defined EXECUTE AS 'LogOnUser' so the privileges at execution time are interpreted from LogOnUser.

The logic in the trigger itself is simple; Count how many sessions are currently connected to the SQL Server from this specific program and using the same login name. If the connection count is more than one, our connection is at least the second one. In this case, the transaction is rolled back which signals to the SQL Server that we have revoked the connection. This prevents the client from establishing the database connection.

The code for connecting the database looks like this:

C#
/// <summary>
/// Connects to the database using the given host name
/// </summary>
/// <param name="hostname">Host name to use</param>
/// <returns>True, if successful</returns>
internal override bool Connect(string hostName) {
   System.Data.SqlClient.SqlConnection connection = null;
   bool ok = true;

   try {
      connection = new System.Data.SqlClient.SqlConnection();
      connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
      connection.Open();
      this.ActiveConnection = connection;
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
      CloseAndDisposeConnection(ref connection);
      ok = false;
   }

   return ok;
}

Actually, that’s not all the code needed for connecting but at this point, let’s focus only on connection attempt. The rest of the code can be found in the download. If the connection succeeds, the program shows the active connection and if it fails, a message box is shown.

So if you start two instances of our test program and in both instances provide correct SQL Server name and connect using Normal connection and Client 1, the first one should succeed but trying the same from the second program running, it should fail giving the following message: "Logon failed for login ... due to trigger execution"

Image 2

Connection Pool Affecting the Connections

Ok, that was a success but if you continue to play with the program:

  • Close the connection from program 1
  • Try to connect using program 2

You still get the same message even though it seems that you’re not connected to the server in the first place. Well, that’s actually not true. While program 1 is still running, the connection you originally established is still alive, it’s just located in the connection pool. If you would end the program, the connection pool would be cleared.

To affect the situation, you have the "Clear all connection pools" – button. Once the connection in program 1 is closed, press the Clear button and try connecting from program 2, it should now succeed.

In our test case, the connection pool was affecting in an undesired way but it’s not the case always. Think about the client application architecture where you:

  • connect
  • do some operations
  • disconnect
  • wait for user input
  • connect
  • do some operations
  • disconnect and so on...

In this situation, when waiting for user input, another session could take the connection and our next attempt could fail. Because of this, it’s reasonable that the connection is kept alive and reserved, not to mention the performance aspects.

Using a Logon Trigger to Allow a Connection Only From a Single Client at Any Given Time

Now let’s make a slight modification. If we want to allow multiple connections for the same login but only from a single client computer at any given same time, let’s modify the trigger.

SQL
-----------------------------------------------------------
-- Create a logon trigger to allow a connection from
-- one host only
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
   @connectioncount int,
   @host1   nvarchar(128),
   @host2   nvarchar(128),
   @message nvarchar(2000);
BEGIN 
   -- Select the amount of connections for the user from different hosts
   SELECT @connectioncount = COUNT(*),
          @host1           = MIN(a.host_name),
          @host2           = MAX(a.host_name)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process      = 1
   AND   lower(a.program_name)  = 'login tester'
   AND    a.original_login_name = ORIGINAL_LOGIN()
   AND    EXISTS(   SELECT 1
                    FROM  sys.dm_exec_sessions b
                    WHERE b.is_user_process = 1
                    AND   b.program_name = a.program_name
                    AND   b.host_name != a.host_name);

   IF  (@connectioncount > 1) BEGIN
      ROLLBACK; 
   END;
END;
GO

The logic is almost the same as previously. I’ve just modified the query to look for connections from different host names. Again if more than one is found, terminate the logon attempt.

Now the client side needs adjustments. As said in the beginning, we are relying on the information the client sends when connecting. In the test program, you can choose if you want to use Client 1 or Client 2 for the connection. Both client names are almost the same, they only have a different client number in the beginning.

But to keep the example as close to real world situation as possible, the program also uses the MAC address of the client computer to identify itself. The MAC address is fetched with the following code:

C#
/// <summary>
/// Returns the MAC address of a network card for client identification purposes
/// </summary>
/// <returns></returns>
internal static string MacAddress() {
   var macQuery = 
     from netInterface in 
          System.Net.NetworkInformation.NetworkInterface.GetAllNetworkInterfaces()
     where netInterface.OperationalStatus == 
          System.Net.NetworkInformation.OperationalStatus.Up
     && netInterface.NetworkInterfaceType != 
          System.Net.NetworkInformation.NetworkInterfaceType.Loopback
     && netInterface.NetworkInterfaceType != 
          System.Net.NetworkInformation.NetworkInterfaceType.Tunnel
     orderby netInterface.Id descending
     select netInterface;

   return macQuery.FirstOrDefault().GetPhysicalAddress().ToString();
}

The idea is that the program uses an address that is operational as it is not a loopback address. In actual situation, you would want to add more checks to the query, for example that the address is meaningful and not an empty string.

Now again, if you connect from both running test programs using Client 1 name, this should succeed. However if you connect using Client 1 name from program 1 and using Client 2 name from program 2, the latter should fail the same way as previously.

So now, we cannot connect from two clients at the same time if they have a different host name. Again, if you close the connection and try to connect from the other one, remember to clear the connection pool in order to succeed.

Communicating with the Client from the Logon Trigger

Okay, we can restrict the logon, but the client always gets the same message. This message doesn’t really explain why the connection is rejected. In some cases, it would be nice to notify the client about the reason. However, this is not possible using traditional mechanisms such as RAISERROR or PRINT. From MSDN:

Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.

That’s a pity but we just have to think of something else. One strategy could be that we have a folder which is shared so that it can be seen by both the SQL Server and the application. If we create a small message file in this folder, we can write the reason there for the client to fetch and show.

The CLR procedure example used in this article is simplified, so if interested, you can read more about writing into a file from the SQL Server in Writing into a file from database. It also explains the steps required to create and register the procedure.

The CLR procedure used in this example looks like this:

C#
public class Logger {
   /// <summary>
   /// Procedure to write a row of data into a specified file
   /// </summary>
   /// <param name="fileName">Full name of file to write into</param>
   /// <param name="line">Line to write</param>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void WriteLine(string fileName, string line) {
      System.IO.FileStream logStream;
      System.IO.StreamWriter logFile;

      using (logStream = new System.IO.FileStream(fileName, 
                                                  System.IO.FileMode.Append, 
                                                  System.IO.FileAccess.Write, 
                                                  System.IO.FileShare.ReadWrite)) {
         using (logFile = new System.IO.StreamWriter(logStream)) {
            logFile.WriteLine(line);
            logFile.Flush();
            logFile.Close();
         }
      }
   }
}

The code is simple, write the line of text to the provided file.

After compiling the logger project, we can register the procedure to SQL Server like this:

SQL
-----------------------------------------------------------
-- Add the CLR procedure
-----------------------------------------------------------
ALTER DATABASE LogonTest SET TRUSTWORTHY ON;
GO

sp_configure 'show advanced options', 1; 
GO 

RECONFIGURE; 
GO 

sp_configure 'clr enabled', 1; 
GO 

RECONFIGURE; 
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'WriteLine')
DROP PROCEDURE WriteLine
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Logger')
DROP ASSEMBLY Logger
GO

CREATE ASSEMBLY Logger 
FROM ' ……\LoginTestExe\Logger\bin\Debug\Logger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000)) 
AS EXTERNAL NAME Logger.Logger.WriteLine;
GO

Since we’re going to use the procedure from the logon trigger, we need to do a few things:

  • add the LogOnUser to the database users
  • grant privileges to execute the procedure to LogOnUser

Like this:

SQL
-----------------------------------------------------------
-- Grant privileges to LogOnUser
-----------------------------------------------------------
USE LogonTest;
GO

CREATE USER LogonUser FOR LOGIN LogonUser;
GO

GRANT EXECUTE ON LogonTest..WriteLine TO LogonUser;
GO

So now, we can make a small adjustment to the logon trigger. See the following:

SQL
-----------------------------------------------------------
-- Create a logon trigger to allow a connection from
-- one host only
-- Write information about rejection reason to a file
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
   @connectioncount int,
   @host1         nvarchar(128),
   @host2         nvarchar(128),
   @message         nvarchar(2000);
BEGIN 
   -- Select the amount of connections for the user from different hosts
   SELECT   @connectioncount = COUNT(*),
         @host1 = MIN(a.host_name),
         @host2 = MAX(a.host_name)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process = 1
   AND   lower(a.program_name) = 'login tester'
   AND    a.original_login_name = ORIGINAL_LOGIN()
   AND    EXISTS(   SELECT 1
               FROM   sys.dm_exec_sessions b
               WHERE   b.is_user_process = 1
               AND      b.program_name = a.program_name
               AND      b.host_name != a.host_name);

   IF  (@connectioncount > 1) BEGIN
      SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126) 
                     + ': ' + @host1 +  ': Simultaneous connection from ' + @host2;
      PRINT @message;
      EXEC LogonTest..WriteLine @fileName='C:\TEMP\ConnectionRejectionInfo.txt', @line=@message

      SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126) 
                     + ': ' + @host2 +  ': Simultaneous connection from ' + @host1;
      PRINT @message;
      EXEC LogonTest..WriteLine @fileName='C:\TEMP\ConnectionRejectionInfo.txt', @line=@message

      ROLLBACK; 
   END;
END;
GO

The code is basically the same as previously but with two additions:

  • The message why the connection was refused is written to SQL Server log because of the PRINT command
  • The same message is written into a file named ConnectionRejectionInfo.txt in C:\TEMP folder

The information from which clients the connection exists is written from the point of view of both clients so that whichever client was trying to establish the connection, a message targeted for it can be found in the file. Another approach could be to rely on the fact that the latter connection is always the failing one, but this may not apply if the logic for rejecting connections is different.

To handle the situation on the program side, let’s have a look at the code making the connection:

C#
/// <summary>
/// Connects to the database using the given host name.
/// If the connection fails fetch the message from shared file
/// </summary>
/// <param name="hostname">Host name to use</param>
/// <returns>True, if successful</returns>
internal override bool Connect(string hostName) {
   System.Data.SqlClient.SqlConnection connection = null;
   bool ok = true;
   string[] logMessages;
   System.Collections.Generic.List<LogMessage> messageList;
   LogMessage myMessage;

   try {
      connection = new System.Data.SqlClient.SqlConnection();
      connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
      connection.Open();
      this.ActiveConnection = connection;
   } catch (System.Exception exception) {
      logMessages = System.IO.File.ReadAllLines(@"C:\TEMP\ConnectionRejectionInfo.txt");
      messageList = new System.Collections.Generic.List<LogMessage>(logMessages.Length);
      foreach (string line in logMessages) {
         messageList.Add(new LogMessage(line));
      }
      var msgQuery = from msg in messageList
                     where msg.MessageTime != null
                     && msg.Host == hostName
                     orderby msg.MessageTime descending
                     select msg;
      myMessage = msgQuery.FirstOrDefault();
      System.Windows.MessageBox.Show(myMessage != null ?
                     myMessage.Message : exception.Message,
                                     ConnectionBase.Title);
      CloseAndDisposeConnection(ref connection);
      ok = false;
   }

   return ok;
}

If the connection fails, the contents of the shared file is read and parsed using LogMessage class. After that, the correct message is found based on client name and the latest timestamp. This message is shown to the user.

Let’s test: If you first connect using Client 1 from program 1 and then using Client 2, take a normal connection with fetch message functionality, you should receive a message like the following:

Image 3

The error message can also be found in SQL Server log

Image 4

Remember that in order for this to work, you need to have a C:\TEMP folder and have access to it or modify both the assembly and the trigger to use some other file. In real situation, you'd use a folder which would be shared to the network and it could be located for example on a file server instead of the database server.

Use a Database Specific Logon Procedure

It’s not always feasible or possible to use logon triggers. For example, think about a consolidated SQL Server instance where several different databases are located. You may not (and should not) have access to server wide functionality and even if you would, there’s no guarantee that creating a logon trigger wouldn’t affect the programs using other databases.

A simple solution is to move the logic in the logon trigger to a procedure and always when connecting to the database, call the procedure.

First, let’s remove the logon trigger so that it does not affect further testing.

SQL
-- remove the logon trigger
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

Now the T-SQL code for the procedure could look like this:

SQL
-----------------------------------------------------------
-- Create a procedure to check existing connections
-- from other hosts. If found, raise an error
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'CheckConnection')
DROP PROCEDURE CheckConnection
GO

CREATE PROCEDURE CheckConnection
WITH EXECUTE AS 'LogOnUser'
AS
DECLARE
   @connectioncount INT,
   @host            VARCHAR(128),
   @user            VARCHAR(128),
   @msg             VARCHAR(128);
BEGIN
   -- Select the amount of connections for the user from different hosts
   SELECT @connectioncount = COUNT(*)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process = 1
   AND   lower(a.program_name) = 'login tester'
   AND   a.original_login_name = ORIGINAL_LOGIN()
   AND   EXISTS(  SELECT 1
                  FROM  sys.dm_exec_sessions b
                  WHERE b.is_user_process = 1
                  AND   b.program_name = a.program_name
                  AND   b.host_name != a.host_name);

   IF (@connectioncount > 1) BEGIN
      SELECT TOP 1
             @user = a.login_name,
             @host = a.host_name
      FROM sys.dm_exec_sessions a
      WHERE a.is_user_process = 1
      AND   lower(a.program_name) = 'login tester'
      AND   a.original_login_name = ORIGINAL_LOGIN()
      ORDER BY a.login_time;
       
      SET @msg = 'User ' + @user + ' already connected from ' + @host;
      RAISERROR(@msg, 15, 1);
     RETURN;
   END;
END;
GO

GRANT EXECUTE ON CheckConnection TO NormalUser;
GO

The logic is basically the same as previously, but now we can use RAISERROR since the context where we are executing the procedure is the successfully established connection. The calling side looks now like this:

C#
 internal override bool Connect(string hostName) {
    System.Data.SqlClient.SqlConnection connection = null;
    bool ok = true;

    try {
       connection = new System.Data.SqlClient.SqlConnection();
       connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
       connection.Open();
       using (System.Data.SqlClient.SqlCommand command =
                          new System.Data.SqlClient.SqlCommand()) {
          command.Connection = connection;
          command.CommandText = "CheckConnection";
          command.CommandType = System.Data.CommandType.StoredProcedure;
          command.ExecuteNonQuery();
       }
       this.ActiveConnection = connection;
    } catch (System.Exception exception) {
       System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
       CloseAndDisposeConnection(ref connection);
       ok = false;
    }

    return ok;
}

After a successful connection, the procedure is called and if it succeeds, continue with the program. Otherwise, show the message why the connection failed.

Use a Database Specific Logon Procedure Without Visibility to Server-Wide System Views

If we take a bit more restricted situation where you have to deal with the fact that no server wide services can be used, we have to have some other kind of alternative. Again, this could be the situation in a consolidated environment where you have no privileges to system views and you can use only information within the dedicated database.

Previously, we could check existing logons from sys owned views but if we’re not allowed to do this, we have to maintain logon information ourselves. The problem is that if we just create a table and think that we:

  • insert a row into it when we enter the database
  • delete it when the program ends

We probably end up in problems very soon. Think about a situation where the program terminates abnormally. In such case, the program isn't able to clean the information from the logon table and when trying to reconnect, we would already have an “active” connection and basically no way to know if the information is accurate or not.

In general, I recommend avoiding reading uncommitted data, but in this specific case, the SQL Server’s ability to read information from an ongoing transaction comes in handy. This happens by using a WITH (NOLOCK) hint in the SELECT statement. As mentioned, in normal cases, this may result in unwanted results so if you have more interest, read What should be considered when NOLOCK hint is used.

Image 5

So the idea is: We first log in to the SQL Server and then read the connect reservations from the reservation table and ignoring the locks. If there is no conflicting connection, we write a new reservation into the table using a transaction started on client side. After this, the client continues with normal operations BUT the transaction is kept open by the program as long as needed to ensure the connection. When the program ends, the transaction is rolled back and the connection holding the transaction is closed.

Why so complicated? The reason is that by using a transaction, we ensure that if the program crashes, the transaction is automatically rolled back by the SQL Server and we have no leftovers in the reservation table.

The table to hold the reservations could look like this:

SQL
-----------------------------------------------------------
-- Reservation table
-----------------------------------------------------------
CREATE TABLE ConnectReservation (
   ID          INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
   Host        VARCHAR(128) NOT NULL,
   Program     VARCHAR(128) NOT NULL,
   UserName    VARCHAR(128) NOT NULL,
   ConnectTime DATETIME     NOT NULL,
   ProcessId   SMALLINT     NOT NULL
);
GO

And now the procedure would be like this:

SQL
-----------------------------------------------------------
-- Procedure
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ReserveConnection')
DROP PROCEDURE ReserveConnection
GO

CREATE PROCEDURE ReserveConnection AS
DECLARE
   @host VARCHAR(128),
   @user VARCHAR(128),
   @msg VARCHAR(128);
BEGIN
   -- Check if a connection from the host already exists
   SELECT @host = cr.Host,
          @user = cr.UserName
   FROM   ConnectReservation cr WITH (NOLOCK)
   WHERE  cr.UserName = ORIGINAL_LOGIN()
   AND    cr.Host     != HOST_NAME();

   IF (@host IS NOT NULL) BEGIN
      SET @msg = 'User ' + @user + ' already connected from ' + @host;
      RAISERROR(@msg, 15, 1);
      RETURN;
   END;

   INSERT INTO ConnectReservation (Host, Program, UserName, ConnectTime, ProcessId)
      VALUES (HOST_NAME(), APP_NAME(), ORIGINAL_LOGIN(), SYSDATETIME(), @@SPID);
END;
GO

As you can see, the procedure makes a simple select to check if a connection from other host already exists. As previously explained, the key is WITH (NOLOCK) hint. In case the row is found, an error is raised, otherwise the reservation is inserted.

If we have a look at the client side, it’s a bit different than the ones before.

C#
private StandardConnection ActiveConnection;
private System.Data.SqlClient.SqlConnection ConnectReservation;
private System.Data.SqlClient.SqlTransaction ReservationTransaction { get; set; }

/// <summary>
/// Connects to the database using the given host name
/// </summary>
/// <param name="hostname">Host name to use</param>
/// <returns>True, if successful</returns>
internal override bool Connect(string hostName) {
   bool ok = true;

   try {
      this.ConnectReservation = new System.Data.SqlClient.SqlConnection();
      this.ConnectReservation.ConnectionString =
                  ConnectionBase.GetConnectionString(hostName);
      if (!ReserveConnection(this.ConnectReservation)) {
         this.ConnectReservation.Dispose();
         return false;
      }
      this.ActiveConnection = new StandardConnection();
      if (!this.ActiveConnection.Connect(hostName)) {
         ConnectionBase.CloseAndDisposeConnection(ref this.ConnectReservation);
         ok = false;
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
      ok = false;
   }

   return ok;
}

/// <summary>
/// Open and reserves a connection to the SQL Server. Existing connections
/// are checked and if not found, a new connect reservation is made
/// </summary>
/// <param name="connection">Connection to use. This should be kept alive and
///    transaction active for desired duration</param>
/// <returns>True if reservation is successful</returns>
private bool ReserveConnection(System.Data.SqlClient.SqlConnection connection) {
   bool ok = true;

   try {
      connection.Open();
      using (System.Data.SqlClient.SqlCommand command =
                         new System.Data.SqlClient.SqlCommand()) {
         command.Connection = connection;
         this.ReservationTransaction = connection.BeginTransaction();
         command.Transaction = this.ReservationTransaction;
         command.CommandText = "ReserveConnection";
         command.CommandType = System.Data.CommandType.StoredProcedure;
         command.ExecuteNonQuery();
         // Leave the transaction open
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
      ConnectionBase.CloseAndDisposeConnection(ref connection);
      ok = false;
   }
   return ok;
}

/// <summary>
/// Close current connections
/// </summary>
override internal bool CloseConnection() {
   if (this.ActiveConnection != null) {
      this.ActiveConnection.CloseConnection();
   }

   if (this.ReservationTransaction != null) {
      this.ReservationTransaction.Rollback();
      this.ReservationTransaction = null;
   }
   CloseAndDisposeConnection(ref this.ConnectReservation);

   return true;
}

So when connecting, the following is done:

  • A transaction is started.
  • The ReserveConnection procedure is called.
  • If successful, the connection containing the active transaction is stored.
  • The actual connection to do database operations is created.

In this scenario, it’s critical to do the actual operations using a separate connection from the one that holds the transaction open. Otherwise, we would commit or rollback the transaction for the reservation at some point of program execution.

Use a Program Authenticated User With Transactional Reservation

One thing we haven’t touched yet is the user authentication. In all of the examples, we’ve used the actual login name from SQL Server for user identification. The login could be either Windows authenticated or SQL Server authenticated but still authenticated by the database server. What if the situation is that the same login is used for different users and the user is actually identified by the program.

In this article, I won’t go into the details about proper user authentication when done by program so I just take it that it’s done properly. The authentication step would precede the connect reservation so you would probably have a nice little procedure to check that the user is known and the hashed password matches the hash stored in the database.

What comes to connect reservation the modification is very small. Instead of using system function ORIGINAL_LOGIN(), we pass the user name to the procedure:

SQL
-----------------------------------------------------------
-- Create a procedure to check existing connections
-- from other hosts based on a reservation table data
-- and with a program identified user
-- If found, raise an error
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ReserveConnectionWithUser')
DROP PROCEDURE ReserveConnectionWithUser
GO

CREATE PROCEDURE ReserveConnectionWithUser @user varchar(128) AS
BEGIN
   SELECT @host = cr.Host
   FROM   ConnectReservation cr WITH (NOLOCK)
   WHERE  cr.UserName = @user
   AND     cr.Host     != HOST_NAME();

   IF (@host IS NOT NULL) BEGIN
      SET @msg = 'User ' + @user + ' already connected from ' + @host;
      RAISERROR(@msg, 15, 1);
      RETURN;
   END;

   INSERT INTO ConnectReservation (Host, Program, UserName, ConnectTime, ProcessId)
      VALUES (HOST_NAME(), APP_NAME(), @user, SYSDATETIME(), @@SPID);
END;
GO

In this variation, the reservation both check and made is made using the supplied user name.

Also in the program, the change is small, we just pass the user name as a parameter to the procedure:

C#
/// <summary>
/// Open and reserves a connection to the SQL Server. Existing connections are checked
/// and if not found, a new connect reservation is made
/// </summary>
/// <param name="connection">Connection to use. This should be kept alive and
/// transaction active for desired duration</param>
/// <param name="userName">User name to make the reservation for</param>
/// <returns>True if reservation is successful</returns>
private bool ReserveConnectionWithUser(System.Data.SqlClient.SqlConnection connection,
                                       string userName) {
   bool ok = true;

   try {
      connection.Open();
      using (System.Data.SqlClient.SqlCommand command =
                                   new System.Data.SqlClient.SqlCommand()) {
         command.Connection = connection;
         this.ReservationTransaction = connection.BeginTransaction();
         command.Transaction = this.ReservationTransaction;
         command.CommandText = "ReserveConnectionWithUser";
         command.CommandType = System.Data.CommandType.StoredProcedure;
         command.Parameters.Add(new System.Data.SqlClient.SqlParameter() {
            ParameterName = "user",
            DbType = System.Data.DbType.String,
            Direction = System.Data.ParameterDirection.Input,
            Value = userName
         });
         command.ExecuteNonQuery();
         // Leave the transaction open
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
      ConnectionBase.CloseAndDisposeConnection(ref connection);
      ok = false;
   }
   return ok;
}

Final Words

I’ve had an interesting time writing this article and I hope you’ll benefit from it. I know I haven’t included all possible variations. For example, one missing variation is to use program authenticated user with the database procedure but in a situation where access to system wide views is allowed. However, as explained in last chapter, the modification is very small, so I didn’t want to bloat the article.

Of course, if you have further ideas or questions, please feel free to leave a comment. But in any case, enjoy coding!

Links and References

History

  • 13th October, 2016: Created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
-- There are no messages in this forum --