Logging sysadmin logins in SQL Server

Our yearly cabin opening “men’s weekend” was last weekend. The fishing was a bit below average, but we still had a great time. I brought my six year old son up, and two of my six year old nephews were brought up by their dad’s as well. The first day of fishing we went over to the rainbow flowage, which was a bit of a bust. We ended up hooking five or six northern pike, but only landed one because they kept biting off the line. The boys in the other boat caught some bluegill and bass, but overall the fishing wasn’t as hot as in years past. This was probably due to all the rain they got up there. There were places where the rivers were over the road. Below is the one pike we managed to land.
Rainbow Flowage NP.jpg
Security these days is as important as ever. SQL Server provides lots of ways to improve security of your data and environment. One small way I like to keep an eye on my system is to log sysadmin logins to the server. Sysadmin is able to do anything in SQL Server, and by reviewing my log from time to time I can be sure that no one is using this type of login to compromise the system.
The first thing to do is to create a table to hold the log data:

USE [DBA_DB]
GO

CREATE TABLE [dbo].[sysadminLogging](
   [SAL_id] [INT] IDENTITY(1,1) NOT NULL,
   [Login] [VARCHAR](255) NOT NULL,
   [LoginDate] [DATETIME2](7) NOT NULL,
 CONSTRAINT [PK_sysadminLogging] PRIMARY KEY NONCLUSTERED 
(
   [SAL_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

All that is left is to create a server level login trigger to record any sysadmin logins to that table:

CREATE TRIGGER [servertrigger_CheckForSysAdminLogin] ON ALL SERVER
    FOR LOGON
AS
    BEGIN
        IF IS_SRVROLEMEMBER('sysadmin') = 1
            BEGIN
                INSERT  INTO DBA_DB.dbo.sysadminLogging
                        ( [Login] , LoginDate )
                VALUES  ( ORIGINAL_LOGIN() , GETDATE() );
            END;
    END;
GO

Check this table regularly to get a good idea of who is logging into your SQL Server with these higher privileges.

EDIT:

james youkhanis points out a problem that can occur in the comments.  This problem could make logging in quite difficult.  I have posted a follow-up where I explain the problem, provide a workaround to allow logging in (as james demonstrates below), and provide an updated trigger to minimize the risk of this occurring.

Advertisements

Credentials in Powershell

I had the opportunity to attend the Madison Fishing Expo a few weekends ago.  It was a great way to stay excited for the upcoming fishing year during these cold winter months.  I didn’t get any new information, but I did let my son pick out a few cheap lures to add to his tackle box.

Choosing fishing lures

The warm weather has completely melted the ice off our area lakes (nice and early!), but we, along with almost the entire rest of the country, got a round of winter weather this week, so we’re back to almost a foot of snow on the ground.  It’ll be at least a few more weeks before I launch the boat for the first time this year.

The company I work for has been in the process of strengthening its security posture for the last few years.  Recently, they took the step of creating separate administrator accounts to use when we are doing things that require administrative permissions.  Up until now, I only had one account – an administrator-level account.  I expected at least a few things to break once they turned off my higher privileges, and those expectations were met.  The thing I’m going to touch on today is Powershell privileges.

I use a Powershell script that is run daily to collect various health statistics regarding my SQL databases and servers.  This script is run from Windows Task Scheduler, and is run from my laptop using my Windows AD account user.  Once that user lost its admin privileges, a few of the collection methods failed.  In order to get them to work, I needed to plug in my admin account for that specific method.  I found a neat way to do that using Powershell’s Credential object.

First, I stored the account password in a text file.  The password is encrypted and placed as a file on the disk by using the following Powershell command:

20170316 Powershell Credential Create Encrypted PW

Opening the new file contains the following:

20170316 Powershell Credential Encrypted PW

So you can see that the password is definitely encrypted.

Now I can reference that file whenever I need to enter credentials.

#Create a credential for connecting to the server
$user = "Domain\adminuser"
$pw = cat "C:\Temp\Password.txt" | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user, $pw

#Access the Disk Info using my new credntial
$disks = Get-WmiObject -ComputerName $instance -Class Win32_LogicalDisk -Filter "DriveType = 3" -Credential $cred;

Using this method you can pass credentials to your Powershell script without having to store them in plain text on your computer. The only downside in my case is I will have to update my encrypted password file whenever my admin account has a password change.

Using Certificates for Cross-Database Access

Having lockable compartments inside your boat is unfortunately pretty important. A couple years ago I was traveling back from a fishing trip with my brother-in-law and we stopped at a gas station. I remained in the truck while my brother went in to get something to drink. We had gotten up very early to fish that morning, and I was not awake, but not all the way sleeping either. Anyway, someone either could tell I wasn’t paying attention or they just didn’t care and they took my tackle box right out of the back of the boat. I had failed to keep it locked up, and as a result I had quite a bit of tackle taken.
Security is a key concern for database administrators. It seems like every week we hear about another large corporation who had customer data stolen. One way to restrict access to data within the database is through the use of Users. A database user has specific permissions set up, and these permissions can be customized from the database level all the way down to specific columns within a table. However, what should you do if you want a user in one database to be able to access data in a different database? In this post I will show how to sign stored procedures with certificates to enable access to objects in other databases.
The first thing to do is to create a certificate. You can put in a password to make it secure, and give it an expiration date far in the future.

CREATE CERTIFICATE ReportingComponentsExecutor_Cert
ENCRYPTION BY PASSWORD = 'Y3110wP3r¢h'
WITH SUBJECT = 'Gugg: For accessing object in other databases', --Goes into the cert metadata
START_DATE = '20150320', EXPIRY_DATE = '2020320'

The next step is to create a user that will be linked to that certificate in the target object. Since this is the user who has the permissions in the target database, the user needs to be created in that database.

CREATE USER [ReportingComponentsExecutor_User] FOR CERTIFICATE [ReportingComponentsExecutor_Cert]
GO

Next we need to give this user the permissions on the database objects that will be needed.

USE TargetDB
GRANT SELECT ON TargetDB.dbo.myTable TO ReportingComponentsExecutor_User;
GRANT UPDATE ON TargetDB.dbo.myTable TO ReportingComponentsExecutor_User;

Lastly, add the certificate to the stored procedure that will be getting the data from the target database.

ADD SIGNATURE TO ReportingComponents.dbo.GetDataFromTargetDB BY CERTIFICATE ReportingComponentsExecutor_Cert WITH PASSWORD = 'Y3110wP3r¢h'

Any change made to the stored procedure will invalidate the signature, so you will have to re-sign it after every change. It can be a good idea to make a note in the stored procedure’s header so anyone making changes will remember to re-sign it. I also like to set up a table with a list of stored procedures that should be signed, and send out an alert each day for any that have become unsigned. Here is a rough example that uses a hardcoded list instead of a table. This uses the stored procedure I showed here that sends dbmail in html table format:

CREATE PROCEDURE [dbo].[VerifySPSignatures] 

AS
BEGIN

   DECLARE @SignedSPs TABLE (SP VARCHAR(100))
   INSERT INTO @SignedSPs
           ( SP )
   VALUES  ( 'InsertPendingTransIntoOMS' ),
           ('UnlockReplenishmentBatch'),
           ('InsertSameShipAddressIntoPickSlip'),
           ('UpdatePLMStyleGradeRuleValues'),
           ('InsertPendingReplenIntoOMS'),
           ('UpdateAdditionalPOInfo'),
           ('UnredeemTestGiftCardsForCSTraining')
   
   ;WITH cte AS (
   SELECT  cer.name AS [Certificate] ,
           pro.name AS [Stored Procedure]
   FROM    ReportingComponents.sys.procedures pro
           INNER JOIN ReportingComponents.sys.crypt_properties cry ON cry.major_id = pro.OBJECT_ID
           INNER JOIN ReportingComponents.sys.certificates cer ON cer.thumbprint = cry.thumbprint)

   SELECT  [@SignedSPs].SP
   INTO ##CertSPTemp
   FROM @SignedSPs
   LEFT OUTER JOIN cte ON cte.[Stored Procedure] = [@SignedSPs].SP
   WHERE cte.[Stored Procedure] IS NULL

   EXEC DBA.dbo.SendDBEmailwithTabularQuery 
       @qSELECT = N'SELECT',
       @fieldlist = N'SP',
       @qFROM = N'FROM ##CertSPTemp',
       @recipients = N'itsupport@domain.com',
       @subject = N'Reporting Stored Procedures Missing Security Certificate',
       @Title = 'From DBA.dbo.VerifySPSignatures: These need to be re-signed using stored procedures in DBA.'

   DROP TABLE ##CertSPTemp
   
END

I also created a stored procedure in my DBA database to help sign these stored procedures as necessary. Since it is in the DBA database it is only accessible to the DBAs, so I don’t have to worry about it being abused.

CREATE PROCEDURE [dbo].[AddSPToOMSCert]
@StoredProcedureSchemaandName VARCHAR(255), --ReportingComponents stored procedure name with schema included
@TargetDBObjectSchemaandName VARCHAR(255), --TargetDB table or view name with schema included
@AccessType VARCHAR(25) --SELECT, DELETE, UPDATE, or INSERT

AS
BEGIN

   DECLARE @SignSQL NVARCHAR(1000)
   DECLARE @FullSignSQL NVARCHAR(1000)
   DECLARE @PermSQL NVARCHAR(1000)
   
   --Sign the stored procedure using the existing certificate
   SELECT @SignSQL = 'ADD SIGNATURE TO ReportingComponents.' + @StoredProcedureSchemaandName + ' BY CERTIFICATE ReportingComponentsExecutor
    WITH PASSWORD = ''''P@ssword123'''''
	SELECT @FullSignSQL = 'USE ReportingComponents; EXEC sp_executesql N''' + @SignSQL + '''';
	EXEC sp_executesql @FullSignSQL

	--Add permission to the OMSObject
	SELECT @PermSQL = 'USE TARGETDB GRANT ' + @AccessType + ' ON TARGETDB.' + @TargetDBObjectSchemaandName + ' TO ReportingComponentsExecutor'
	EXEC sp_executesql @PermSQL
		
END

GO'

This simplifies the process of signing a stored procedure.
I love looking at examples to demonstrate how something works, so let’s set one up. Let’s assume we have two databases, Gamefish and Baitfish. I have a user in Gamefish, Muskie, who I want to be able to see into a table in Baitfish, but only through the use of a stored procedure. Here’s how I’d set it up.  It is important to note that the same certificate must exist in both databases.  This means we must export the cert created in the first database and import it into the second.

--Create the databases
USE MASTER
CREATE DATABASE Baitfish;
CREATE DATABASE Gamefish;
GO

--Create the login that will be testing permissions
CREATE LOGIN [Muskie] WITH PASSWORD=N'Password123'
GO

--Create the cert
USE Baitfish
CREATE CERTIFICATE CrossDBReportingCert
ENCRYPTION BY PASSWORD = 'B1u3g1LL'
WITH SUBJECT = 'Gugg: For accessing object in other databases', --Goes into the cert metadata
START_DATE = '20150320', EXPIRY_DATE = '20200320'
GO

--Export the cert so we can import it into the other database
BACKUP CERTIFICATE CrossDBReportingCert TO FILE = 'C:\temp\CrossDBReportingCert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\CrossDBReportingCert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'B1u3g1LL',
                  DECRYPTION BY PASSWORD = 'B1u3g1LL')
GO

--Create the user off the cert
CREATE USER CrossDBReportingUser FROM CERTIFICATE CrossDBReportingCert
GO

--Create the table in baitfish
CREATE TABLE dbo.prey (id INT IDENTITY(1,1), fishname VARCHAR(50))
INSERT INTO dbo.prey
        ( fishname )
VALUES  ( 'whitefish'),('cisco'),('sucker')
GO

--Grant read-only permissions to the new user for dbo.prey
GRANT SELECT ON dbo.prey TO CrossDBReportingUser
GO

--Switch over to the Gamefish Database
USE Gamefish
GO

--Create a user who will have no permissions in the Baitfish database, but full access to the Gamefish DB
CREATE USER [Muskie] FOR LOGIN [Muskie]
EXEC sp_addrolemember N'db_owner', N'Muskie'
GO

--Create a Stored Procedure to get the fish names out of Baitfish.dbo.Prey
CREATE PROCEDURE dbo.GetBaitfishPrey
AS

BEGIN
   SELECT fishname
   FROM Baitfish.dbo.prey
END

GO

--Cert must exist in both databases
CREATE CERTIFICATE CrossDBReportingCert FROM FILE = 'C:\temp\CrossDBReportingCert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\CrossDBReportingCert.pvk',
                  DECRYPTION BY PASSWORD = 'B1u3g1LL',
                  ENCRYPTION BY PASSWORD = 'B1u3g1LL')
GO

-- Delete the file with the certificate, we don't need it anymore.
EXEC MASTER..xp_cmdshell 'DEL C:\temp\CrossDBReportingCert.*', 'no_output'
GO

--Run as Muskie user
EXECUTE AS LOGIN = 'Muskie'
GO

--Try to get results before signing procedure with certificate
EXEC dbo.GetBaitfishPrey
GO

20150324 Cross DB Permissions Failed

REVERT
GO

--Next sign procedure with certificate
ADD SIGNATURE TO dbo.GetBaitfishPrey BY CERTIFICATE CrossDBReportingCert WITH PASSWORD = 'B1u3g1LL'
GO

--Run as Muskie user
EXECUTE AS LOGIN = 'Muskie'
GO

--Successfully get results from stored procedure
EXEC dbo.GetBaitfishPrey
GO

20150324 Cross DB Permissions Success

REVERT
GO

--Cleanup
USE MASTER
DROP LOGIN Muskie
DROP DATABASE Gamefish;
DROP DATABASE Baitfish;
GO

So there is an example that I believe is easy to understand. Use this as a model to set up your own cross database permissions.