Understanding how SQL Server handles Service Isolation
Learn how SQL Server handles service isolation for logins tied to NT service accounts.
Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. Brian currently serves as a data architect as well as an independent infrastructure/security architect concentrating on Active Directory, SQL Server, and Windows Server. He has served in a myriad of other positions including senior database administrator, data warehouse architect, web developer, incident response team lead, and project manager. Brian has spoken at 24 Hours of PASS, IT/Dev Connections, SQLConnections, the Techno Security and Forensics Investigation Conference, the IT GRC Forum, SyntaxCon, and at various SQL Saturdays, Code Camps, and user groups.
- MSSQLTips Awards: Author of the Year Contender - 2015, 2017 | Champion (100+ tips) - 2014
Using Triggers to Mitigate Some SQL Injection Effects
I support a web application and we've determined is vulnerable to SQL injection. Unfortunately, there's no real ability to modify the application in a timely manner and we can't take it down. In this tip we show what can be done to help mitigate some SQL
How to Enable Secondary Decryption of SQL Server Symmetric Keys
In this tip we look at how to setup secondary encryption and decryption keys that can allow a user to access data that was encrypted by multiple methods.
Using Views to Expose Encrypted Data in SQL Server
I have used SQL Server's built-in encryption to hide data in one of my SQL Server databases, such as demonstrated in this article: SQL Server Encryption to Block DBAs' Data Access. The problem is that this is a reporting system and my end users need to be
Determining Permission Issues for a SQL Server Object
I have a security problem. Users have the ability to access the tables in a database with one exception. No matter what permission I grant or what role I give within the database, users are still denied access. I've even made users a member of the db_owne
Determining Drive Letters in Use by SQL Server Databases
I am auditing my SQL Server environment and I need to determine what drive letters are in use by each instance. I know I can query sys.database_files for each database. Is there a way to do this for all databases?
SQL Server Security Community Questions on Windows, Server Level, Database, Roles and more
I had the privilege to attend K. Brian Kelley's MSSQLTips.com Q&A session on SQL Server security. I understand he was bombarded with security questions about Windows, Active Directory, Service Accounts, SQL Server Server Level, SQL Server Database Level,
What to Audit at the SQL Server Instance Level
I'm responsible for the security of my SQL Servers. I know I should be auditing them regularly, I just don't know where to start. In this tip we look at what should be audited for SQL Server server level permissions.
Understanding the importance of key length with the SQL Server asymmetric encryption algorithms
I'm trying to use SQL Server's built-in encryption and I see there are three different options available for an asymmetric key, corresponding to key length. In this tip we cover which option to use.
Understanding the SQL Server Symmetric Encryption Algorithms
I'm trying to use SQL Server's built-in encryption and I see there is an assortment of algorithms available. What is the difference between each one? In this tip we cover all of the options.
Understanding the SQL Server HASHBYTES hashing algorithms
I'm trying to use the HASHBYTES() function and I see there is an assortment of hashing algorithms available. What is the difference between each one? In this tip we cover which option to use.
Can I stop a System Admin from enabling SQL Server xp_cmdshell?
We have an audit requirement to disable xp_cmdshell. However, I've read that a member of the sysadmin role can re-enable xp_cmdshell. Is there any way to prevent this from happening?
Disaster Recovery Planning for Microsoft SQL Server – Getting Backups and Restores Right
I'm responsible for ensuring that my SQL Servers can be recovered properly in the event of a disaster. I've heard a lot of the acronyms, but how do I apply this to practical steps with respect to my environment? What else do I need to know other than the
SQL Server Case Sensitive Collations and DMVs
We recently instituted a new database and it has a case-sensitive collation due to the application requirements. We have several monitoring scripts that check the health of our SQL Servers and their databases by querying particular dynamic management view
The Power of the SQL Server Database Owner
I have a couple of databases supporting applications. The first application requires that it's user be a member of the db_owner role. The second application actually has to own the database. What are the effects of having such permissions? Check out this
Blocking SQL Server db_datareader, db_datawriter, and db_owner Permissions
I'm in a dilemma. In a database I currently support, the application makes use of db_datareader and db_datawriter to give permissions to the tables. In a different database, it's even worse as the application uses db_owner. We're adding new tables to both
Understanding GRANT, DENY, and REVOKE in SQL Server
I'm having a bit of trouble understanding how GRANT, DENY, and REVOKE work together in SQL Server, especially when you consider things like schemas. Which setting trumps the others? How do I undo security? In this tip we look at an example of how these wo
SQL Server Agent Job Email Notifications via PowerShell
I want to be able to be notified by email when I have a SQL Server Agent job fail. However, I've been told that for whatever reason, I cannot use Database Mail or SQL Mail. Do I have any other options? Check out this tip to learn more.
SQL Server Encryption To Block DBAs Data Access
I have a requirement to encrypt the data within a database, but I cannot let the DBAs see the data. I could build encryption routines into the application, but I'd prefer to use SQL Server's built-in encryption. Is there a way I can do this? Check out th
How to Automate SQL Server Restores for a Test Server
I know that if you don't test a database backup, you don't know if it's good or not. I'd like to automate not only the backup of the database, but also restoring of the backups to a test server. There's no money in the budget for tools to assist me. How c
How to Identify and Backup the Latest SQL Server Database in a Series
I have to support a third party application that periodically creates a new database on the fly. This obviously causes issues with our backup mechanisms. The databases have a particular pattern for naming, so I can identify the set of databases, however,
Auditing SQL Server 2012 Server Roles
My organization is looking at SQL Server 2012 and I know that the ability to create roles at the server level is a new feature. Since this is new and impacts security, how do I handle them and how do I audit them? In this tip we look at some queries that
Viewing VMware Counters in PerfMon for SQL Server
I want to ensure that my SQL Server on a VMware Guest OS is getting the resources it should. This would go a long way towards helping isolate the performance problems we're experiencing. However, our system administrators won't give us access to VirtualCe
Protecting the SQL Server Backup folder
I want to backup my SQL Server databases to a folder, but I want to minimize who has access to the folder. In other words, I want to make sure that members of the Windows Local Administrators group don't get to the backups without intentionally trying to