Prevent SQL Logins from using SSMS … or any other app

In a continuation of my previous LOGON TRIGGER post.

A downside to SQL logins is that anyone with the username / password combination can login as that user. This prevents us from knowing who truly logged in and performed some action. It is possible to trace and log everything by IP address and correlate back to that.

Another downside is these accounts are usually service accounts, at least in our environment. These SQL logins may have more permissions than they should because they may be part of a vendors product, and the vendor’s app requires them to have sysadmin on the server. We have an issue with some of our users logging in as these SQL logins and running queries or manipulating data that they shouldn’t be.

So how can we allow the login to still exist, but deny normal users from logging in under those credentials? There’s a few different ways, one could be isolating these users to specific endpoints that would allow only that user from a given ip range. Another option, and the one that made sense here, was to use a LOGON TRIGGER. This allows us a great amount of flexibility on deciding when to prevent a connection from happening.

In this case, we wanted any SQL login using Management Studio to be denied access. Any of the other apps that log in with this account will still work. This includes SqlCmd, Access, etc. Our main problem, though, were devs coming in under SSMS. We also wanted to log any login attempts using this method.

This script creates a logging table in our management database (T1001), creates a view to make things nicer then creates the logon trigger to reject any connections that match our criteria.

-- Create table to hold rejected login attempts
USE T1001;
CREATE TABLE T1001.SQLTrace.loginData (
	data XML,
	program_name sysname
-- Create view to make querying the table a little nicer
CREATE VIEW SQLTrace.loginDataView
      ,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/SID)[1]', 'nvarchar(85)') AS SID
      ,data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname') AS ClientHost
      ,data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit') AS IsPooled
FROM SQLTrace.loginData
-- Create logon trigger
USE [master];
/****** Object: DdlTrigger [Deny_SQLLogin_SSMS_Trigger] ******/
	SELECT * FROM master.sys.server_triggers
	WHERE parent_class_desc = 'SERVER'
		AND name = N'Deny_SQLLogin_SSMS_Trigger')
DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType 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')
IF @AppName LIKE ('Microsoft SQL Server Management Studio%') --If it's SSMS
   AND @LoginName <> 'sa'
   AND @LoginType = 'SQL Login' --('SQL Login' | 'Windows (NT) Login')
    ROLLBACK; --Disconnect the session
    --Log the exception to our table
    INSERT INTO T1001.SQLTrace.loginData(data, program_name)
	VALUES(@data, @AppName)

Don’t forget to create a job to purge historical log data at sufficient intervals for you.

Locking While Dropping or Altering an Index

Yesterday I was trying to drop some hypothetical indexes in production against a fairly active table. I started causing blocking so I had the bright idea of disabling the indexes first, then dropping. Well, that didn’t help, even when setting DEADLOCK_PRIORITY to LOW. I ended up waiting until early morning to remove those indexes to prevent from blocking other users.

Finding no info on the web about the locks taken during the process of dropping or disabling an index, I set about doing this small bit of research.

The queries I used:
In Window 1 (Execute first)


* I use TABLOCKX to simulate many updates going to this table.

In Window 2

DROP INDEX [ix_myTable_testIndex] ON myTable

Using Adam Machanic’s (blog | twitter) sp_WhoIsActive I was able to get the lock details easily.

sp_whoisactive @get_locks = 1

When you disable an index, you end up trying to acquire a Sch-M or Schema Modification lock.

<Database name="Sandbox">
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Object name="myTable" schema_name="dbo">
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />

Then the next select query comes along and trys to grab an IS or Intent Shared lock

<Database name="Sandbox">
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Object name="myTable" schema_name="dbo">
        <Lock resource_type="OBJECT" request_mode="IS" request_status="WAIT" request_count="1" />

So at this point yesterday, I was thinking, maybe I can disable the index and then drop it. Well, ALTER INDEX … DISABLE also waits on a Sch-M lock before it can proceed. I’m not any better off. Even after the disabling the index, SQL Server still tries to grab a Sch-M lock to drop the disabled index. All these rules also seem to apply to hypothetical indexes. I’m sure there’s some internals reason why it does this, but why worry about locks when dropping a disabled or hypothetical index since neither are used by active queries?

Lesson learned, drop indexes when the table usage is low.

[qtweet 68491945105821696]

Disable SQL Agent Jobs with PowerShell

I had a need today to disable all of our admin jobs while I moved our admin database to another file location. We didn’t want to get a lot of alerts or job failures during the move, even though it took less than 5 minutes. This script runs best in sqlps or by adding the appropriate snap-ins to your regular console.

cd SQLSERVER:\SQL\[server]\[instance]\JobServer\Jobs
dir | ?{$_.Name.StartsWith('!Admin')} | %{$_.IsEnabled = $false; $_.Alter()} #rerun with $true to enable

Get all SERVERPROPERTY values for SQL Server

A quick script to grab all the SERVERPROPERTY() values from a SQL instance in a table. Useful when you forget what the propertyname options are. Properties obtained from

DECLARE @props TABLE (propertyname sysname PRIMARY KEY)
INSERT INTO @props(propertyname)
SELECT 'BuildClrVersion'
SELECT 'Collation'
SELECT 'CollationID'
SELECT 'ComparisonStyle'
SELECT 'ComputerNamePhysicalNetBIOS'
SELECT 'Edition'
SELECT 'EditionID'
SELECT 'EngineEdition'
SELECT 'InstanceName'
SELECT 'IsClustered'
SELECT 'IsFullTextInstalled'
SELECT 'IsIntegratedSecurityOnly'
SELECT 'IsSingleUser'
SELECT 'LicenseType'
SELECT 'MachineName'
SELECT 'NumLicenses'
SELECT 'ProcessID'
SELECT 'ProductVersion'
SELECT 'ProductLevel'
SELECT 'ResourceLastUpdateDateTime'
SELECT 'ResourceVersion'
SELECT 'ServerName'
SELECT 'SqlCharSet'
SELECT 'SqlCharSetName'
SELECT 'SqlSortOrder'
SELECT 'SqlSortOrderName'
SELECT 'FilestreamShareName'
SELECT 'FilestreamConfiguredLevel'
SELECT 'FilestreamEffectiveLevel'
SELECT propertyname, SERVERPROPERTY(propertyname) FROM @props

Notes for Moving BizTalk’s Databases

Yesterday, I was tasked with helping a team move their BizTalk databases to another server. These are my notes for anyone else in this predicament.

In our case we had 6 databases to move:

  • BizTalkDTADb
  • BizTalkHwsDb
  • BizTalkMgmtDb (This is the management database that tells BizTalk where everything else is.)
  • BizTalkMsgBoxDb
  • BizTalkRuleEngineDb

We had originally moved just 5 of these, not realizing that SSODB was part of the pack. Once the dbs were moved, the engineer worked on getting the service talking to the management database. We were still getting errors about connecting. After a little Googling of MSDN, I found there was some changes that needed to be made to the registry and database. The engineers handled the registry, I handled the data changes. Here’s a script of all the data changes I had to make.

USE BizTalkMgmtDb;
UPDATE dbo.adm_Group
SET TrackingDBServerName = 'newServerInstance'
    ,SubscriptionDBServerName = 'newServerInstance'
   --More columns exist in this table for other servers
   --but these are just the two we needed to change
UPDATE dbo.adm_OtherDatabases
SET ServerName = 'newServerInstance'
UPDATE dbo.adm_OtherBackupDatabases
SET ServerName = 'newServerInstance'
UPDATE dbo.adm_MessageBox
SET DBServerName = 'newServerInstance'

There was a lot more to this move than just what I’ve put down, but this is the extent of the database changes.

SQL Saturday #63 – Dallas: Wrap-up

SQL Saturday #63 – Dallas was a great event. The venue was great, although parking was a little unclear (I had to walk all the way around the building to get in.) Breakfast was good, but I’m always disappointed when there are no breakfast juices, just coffee, cola or water.

I Attended Rob Sullivan’s (blog | twitter) session on ORMs. I always like having a different perspective of topics I talk about. He did a good job of showing what to watch out for, but not demonizing ORMs as is often done. David Stein’s (blog | twitter) talk on Zombie Databases was excellent. David was very entertaining to listen to. If you ever get a chance to hear him present, do so.

After lunch it was time for my session, “So What can I do with Powershell ?” (Resources) I got off to a good start: [qtweet 54249436800483328] I was trying to make a clean joke and this came out instead. Oops. Other than that misstep, my session went really well. I was getting plenty of questions and interaction. I think this session was better since I used feedback given by Paul Randal (blog | twitter) when I gave a short presentation in front of him. Stay in front of the audience, not your computer and don’t block your mouth with your hands. I found I’m a much better presenter when I’m standing as it corrects both of those issues.

The gelato breaks were awesome!

I spent an hour talking with Idera about their interaction with the community and it sounds like they’re starting to get it. Quest, Redgate, Confio, and SQL Sentry have been doing community for a while and it’s nice to see the other big vendor get in the game as well.

During the closing ceremonies and raffle, I won a freaking iPad 1 being given away by Fusion-io. Before I had sat down from receiving my prize, I had won the Telerik Ultimate Collection. Talk about lucky, since “I never win anything!!”. I do have intentions to give away the Telerik collection to someone who’ll really use them, after all, what’s a DBA going to do with RAD controls. If you or someone you know does UI development, or forced to do UI development without any help, let me know.

Thanks to NTSSUG (blog | twitter) for putting on a great event.

Change SQL Startup Parameters with PowerShell

Backups Abound

The Annoyance

Sometimes when we’re trying to track down a problem and looking through SQL’s Logs we have to dig through hundreds of backup successful events just to find what we’re looking for. Unfortunately, SSMS doesn’t have an exclusion filter when viewing the logs, so I can’t filter them out that way. We also don’t care about having successful backups in these logs as the info is available elsewhere, including our management database.

The Solution

Trace flag 3226 will turn off logging of successful backups to the SQL Server error log, in addition to the Windows system event log. There are two ways to turn this on. Using the T-SQL command DBCC TRACEON(3226,-1), you immediately enable it, but only until SQL restarts as flags set this way do not survive a service restart. The other way to set it is by using startup parameters for the SQL service.

Don't Forget the Semicolons

In order to change SQL’s startup parameters, you need to:

  1. Load SQL Server Configuration Manager
  2. Right-click and bring up the Properties for the service you want to edit
  3. Click the Advanced tab
  4. Edit the Startup Parameters property by appending -T3226, remembering to separate with semicolons
  5. Click OK

The Better Solution

I wanted to do this quick and easy without all the clicks. I knew I wouldn’t just do this on one server, but maybe tens if not a hundred. So I opened up PowerShell ISE and started cranking out a script to this for me. My first hurdle was how are these startup parameters stored and how can I actually get to them to edit them. It turns out, they are just stored as registry keys. PowerShell works with the registry real well. After finding the key locations, doing some logic to make sure I change all instances on that server, I had my script. I made it generic enough so you could add any startup parameter to SQL, not just trace flags.

Calling the script to update all instances on the current computer is as easy as

PS> .\Add-SqlServerStartupParameter.ps1 '-T3226'


The current version does not support SQL 2000, though I plan to release an update that will. I also plan to release a version that supports remote servers. If you are changing a clustered instance, make sure to change it on node the instance is running on as clustered instances overwrite registry settings with whatever their last values were when the instance was running. It’s best to change all nodes in a cluster at once, to be on the safe side.

Download the script: Add-SqlServerStartupParameter