SQL SERVER – Beginning of SQL Server Security – Guest Post

Security is a very important concept and no matter how many times we discuss this it is never enough. I have requested my friend Bharti who is expert in SQL Domain to help me write  a simple post about beginning SQL Server security. I personally found it very easy read and good beginning on this subject. Bharti Batra is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.


Today all organizations have important data that is confidential and mission-critical, and should not be disclosed to unauthorized persons. Hence the security of data is one of the prime aspects of a DBA’s job.

We will discuss about the SQL server security model and different features that SQL provides for data security.

Security means that only relevant data should be available to authenticated persons. It additionally includes appropriate restrictions on the use of data, as well as controls to prohibit data theft. In case data is stolen by unauthorized users, security systems do not allow them to make use of it.

However it is not sufficient to just secure the SQL server. The operating system’s network security is also important. In addition, physical access to the server must be restricted and the application which accesses SQL server must be authenticated.

The security model has five layers, which are Physical security, OS and Network security, Database security, Data security and Application Security. Below mentioned are the specifications for each one of them.

Physical security: Physical access to server should be restricted. This can be done by guards outside the server room, biometric locks like retina scan or finger print reader.

OS and Network Security: Provide only restrictive or no access to server over network. Use firewall to restrict unauthorized access from network. Use less privileged service accounts. And SQL server data files, logs are stored on windows file system. So restrict access to these file locations. Always keep your system up to date with service packs and patches.

SQL Server Security:  Access to SQL Server must be restricted by authenticated users. SQL server supports two authentication mechanisms: – Windows authentication and SQL Server authentication.

Windows authentication is secure for most environments. With this we can grant login rights to the database to a Windows login or a Windows group.

In SQL Server authentication, the user has an SQL login that is not mapped with any Windows login.

There are some important terms in SQL server security, which include:

Principals are that require access to SQL server or database, to which we grant permissions for example: logins, roles etc.

Securables are items we secure or ones on which we grant permissions to principals. For example, database, schemas, table etc. The below figure gives you details about the principals and securables.

Refer the Permissions Hierarchy Image

Login: At server level we have logins, which give access to a user to SQL Server. Server level permissions are assigned to logins. Login can be windows login or SQL server login, depending upon the authentication mechanism you are using.

Example: Creating a login

To create an SQL login named SqlLogin with password ‘Password@123’ by T-SQL, the command is:

To create a windows authenticated login, the T-SQL command is:

To create a login from Management studio

Open object explorer and then go to security folder at instance level and then right click login to create a new login

Fill the form to create a new login

Users:  The logins are mapped with users in database and the database level permissions are assigned to users. There are some predefined user present in every database which are dbo, guest. ‘dbo’ is the default user for sysadmin login.

Example: Creating a user

Create a user by T-SQL command:

Create a user by Management studio

In object explorer, go to the security folder inside the database folder and right click users for new users

Fill the form

Roles: There are some built-in roles present at server and database level. These roles contain predefined set of permissions so if you want to assign specific set of permissions to a login or user, make them the member of a role. There are two types of roles:

Fixed server roles: These server roles are predefined and you cannot create a new one.

To make a user member of Server Role, T-SQL command is:

Database Level Roles: At database level, we have some predefined database level roles. However users can create their own database roles.

To make a user member of Database Role, T-SQL command is

By these means we can restrict access to our database, however placing restrictions is not enough because data can also be stolen. Critical data must be encrypted for security so that even if it is stolen, it cannot be used. SQL server encryption mechanism to encrypt the data by using certificates, keys.

Encryption:

The SQL server provides hierarchical encryption structure, as shown in the image below:

Refer the Encryption Hierarchy Image

The top key, i.e. service master key is encrypted with windows Data Protection API (DPAPI). And at database level we have Database Master Key that is encrypted with the Service Master Key. So the encryption in SQL Server is layered where the upper layer encrypts the layer below it. Data is encrypted by keys, Symmetric (Public key) or Asymmetric (public, private key pair) and these keys get encrypted by certificates, which are encrypted by database master key. Data can also be directly encrypted by certificates that have more information than keys like the name of issuing authority, subject and expiry date.

Data can be encrypted, and the keys and certificates are in the database. But what if the backup file or mdf file gets stolen?

For that we have Transparent Database Encryption (TDE), which is a real-time IO encryption that uses certificates and Database encryption key. This is done for encrypting the database, so in case the backup or mdf file is stolen, it cannot be used fully without the certificate.

There are other important considerations too. For example, just applying the security is not enough. Auditing also holds huge significance. SQL server provides SQL Server Audit for auditing purpose. SQL Server Audit can track and log events that occur at the server level or the database level automatically. With SQL server audits, several server and database level actions can be tracked. These include failed login attempts, modification in structure of database or table etc. These can then be logged in Windows event log or a file system file.

Guest Author 

The author of the article is Bharti Batra, Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Solution – User Not Able to See Any User Created Object in Tables – Security and Permissions Issue

There is an old quote “A Picture is Worth a Thousand Words”. I believe this quote immensely. Quite often I get phone calls that something is not working if I can help. My reaction is in most of the cases, I need to know more, send me exact error or a screenshot. Until and unless I see the error or reproduce the scenario myself I prefer not to comment. Yesterday I got a similar phone call from an old friend, where he was not sure what is going on. Here is what he said.

“When I try to connect to SQL Server, it lets me connect just fine as well let me open and explore the database. I noticed that I do not see any user created instances but when my colleague attempts to connect to the server, he is able to explore the database as well see all the user created tables and other objects. Can you help me fix it? “

My immediate reaction was he was facing security and permission issue. However, to make the same recommendation I suggested that he send me a screenshot of his own SSMS and his friend’s SSMS. After carefully looking at both the screenshots, I was very confident about the issue and we were able to resolve the issue. Let us reproduce the same scenario and many there is some learning for us.

Issue: User not able to see user created objects

First let us see the image of my friend’s SSMS screen. (Recreated on my machine)

Now let us see my friend’s colleague SSMS screen. (Recreated on my machine)

You can see that my friend could not see the user tables but his colleague was able to do the same for sure. Now I believed it was a permissions issue. Further to this I asked him to send me another image where I can see the various permissions of the user in the database.

My friends screen

My friends colleagues screen

This indeed proved that my friend did not have access to the AdventureWorks database and because of the same he was not able to access the database. He did have public access which means he will have similar rights as guest access. However, their SQL Server had followed my earlier advise on having limited access for guest access, which means he was not able to see any user created objects.

My next question was to validate what kind of access my friend’s colleague had. He replied that the colleague is the admin of the server. I suggested that if my friend was suppose to have admin access to the database, he should request of having admin access to his colleague. My friend promptly asked for the same to his colleague and on following screen he added him as an admin.

You can do the same using following T-SQL script as well.

USE [AdventureWorks2012]
GO
ALTER ROLE [db_owner] ADD MEMBER [testguest]
GO

Once my friend was admin he was able to access all the user objects just like he was expecting.

Please note, this complete exercise was done on a development server. One should not play around with security on live or production server. Security is such an issue, which should be left with only senior administrator of the server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database

I have written a few articles recently on the subject of guest account. Here’s a quick list of these articles:

One of the advices which I gave in all the three blog posts was: Disable the guest user in the user-created database. Additionally, I have mentioned that one should let the user account become enabled in MSDB database. I got many questions asking if there is any specific reason why this should be kept enabled, questions like, “What is the reason that MSDB database needs guest user?” Honestly, I did not know that the concept of the guest user will create so much interest in the readers. So now let’s turn this blog post into questions and answers format.

Q: What will happen if the guest user is disabled in MSDB database?
A:  Lots of bad things will happen. Error 916 - Logins can connect to this instance of SQL Server but they do not have specific permissions in a database to receive the permissions of the guest user.

Q: How can I determine if the guest user is enabled or disabled for any specific database?
A: There are many ways to do this. Make sure that you run each of these methods with the context of the database. For an example for msdb database, you can run the following code:

USE msdb;
SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

There are many other methods to detect the guest user status. Read them here: Detecting guest User Permissions – guest User Access Status

Q: What is the default status of the guest user account in database?
A: Enabled in master, TempDb, and MSDB. Disabled in model database.

Q: Why is the default status of the guest user disabled in model database?
A: It is not recommended to enable the guest in user database as it can introduce serious security threat. It can seriously damage the database if configured incorrectly. Read more here: Disable Guest Account – Serious Security Issue

Q: How to disable guest user?
A: REVOKE CONNECT FROM guest

Q: How to enable guest user?
A: GRANT CONNECT TO guest

Did I miss any critical question in the list? Please leave your question as a comment and I will add it to this list.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Detecting guest User Permissions – guest User Access Status

Earlier I wrote the blog post SQL SERVER – Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.

Here are the comments of Manoj:

1) How do we know if the uest user is enabled or disabled?
2) What is the default for guest user in SQL Server?

Default settings for guest user

When SQL Server is installed by default, the guest user is disabled for security reasons. If the guest user is not properly configured, it can create a major security issue. You can read more about this here.

Identify guest user status

There are multiple ways to identify guest user status:

Using SQL Server Management Studio (SSMS)

You can expand the database node >> Security >> Users. If you see the RED arrow pointing downward, it means that the guest user is disabled.

Using sys.sysusers

Here is a simple script. If you notice column dbaccess as 1, it means that the guest user is enabled and has access to the database.

SELECT name, hasdbaccess
FROM sys.sysusers
WHERE name = 'guest'

Using sys.database_principals and sys.server_permissions

This script is valid in SQL Server 2005 and a later version. This is my default method recently.

SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

Using sp_helprotect

Just run the following stored procedure which will give you all the permissions associated with the user.

sp_helprotect @username = 'guest'

Disable Guest Account

REVOKE CONNECT FROM guest

Additionally, the guest account cannot be disabled in master and tempdb; it is always enabled. There is a special need for this. Let me ask a question back at you:

In which scenario do you think this will be useful to keep the guest, and what will the additional configuration go along with the scenario?

Note: Special mention to Imran Mohammed for being always there when users need help.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.

Yesterday I wrote a blog post discussing how guest user can become security threat. The script which was demonstrated for the example had a small T-SQL query which creates a new user. Later, I got an email from a user who had created this scenario on his production environment. It makes me sad that I had clearly talked multiple times about how to execute this as a trial on a development server or a test server, but NOT on a production server. Anyway, here is the email:

“Pinal,

I ran your test on our production server as our development server was busy. Now when I try to drop the test user, it gives me the following error:

Msg 15434, Level 16, State 1, Line 1
Could not drop login ‘test’ as the user is currently logged in.

Could you please help me drop this test user? I cannot restart the server as it is a production server, and I need to drop this user as fast as I can. First of all, I have closed all the connections but I do not know which connection is still open. I just need to identify which connection is open and kill it if required. In any case, I need to drop it before my supervisor catches me.”

This email I read really felt very awkward. Here is my answer. I hope you do not find it rude.

“Dear Friend,

First of all, I want to ask why you ran the demo on your production server. You must not execute it on a production server. It was just for the purpose of demonstration. This reminds me of an email I received from one of my friends that one should never post details which can harm a certain production server, as there are people who do not follow the advice no matter how many times you warn them.

Trust me – your supervisor will have configured audits and he will soon discover this test user, whether you tell him or not. I suggest that instead of hiding, you go and tell him everything in the most honest way you can.

Here is one more tip for you – killing any sessions without permission from a user or a senior admin will get you in trouble.

Once again I do not want your server to be exposed to potential security threats while your supervisor is away, so follow the steps mentioned below to drop the test user:

Find an active connection using the test user:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'

Kill the active connection using the test user:

KILL 52 -- Replace 52 with the your session ids received from earlier query

Drop the test user:

DROP LOGIN test

Hopefully you’d keep your production server away from executing scripts meant to run on a development server.”

Well, after writing the email I did not feel good as I did not like to sound rude. However, I kept telling myself that it was needed. What do you think about this whole conversation?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Disable Guest Account – Serious Security Issue

“No Guests PLEASE!”

“Doesn’t your Indian tradition suggest welcoming guests and treating them in the best way possible?”

“Yes, but I am talking about the Guest user in SQL Server.”

Oh!”

This was a real conversation that happened a couple of years ago. I welcome guests as much as any other Indian does; however, I am strongly opinionated about guest user in SQL Server. I like to keep it disabled unless there is a special need of it. If there is some persistent need of a guest user, I suggest to create separate account. Again, there are always special cases where there is a need of this guest account. But in other cases, a guest account is not necessary.

A Real World Story

Today I am in the mood to tell a real life story.

About 2 years ago, I got a call very late in the evening from one of my friends. He said that his database was compromised and so he needed to talk to me. He described the situation to me – that his database had a single user (himself), but without his knowledge, his database was often observing non-permitted activities. He was very scared.

When there is no real clue, most companies take the database down or turn it offline to prevent any further damage to the system.

Just like the same, he decided to disconnect the database from other applications, as well as to move th behind firewall of the system. After making sure that there is no other application connected even at behind the firewall, there was still this inappropriate activity going on. He became more worried, and finally he sought my help.

I decided to take up the challenge to help him. After a while, we discovered that he had the GUEST user enable on his database. Even though he had already removed all the other accounts, he missed to remove the guest user account. As the guest user was enabled, any user who had permission to server was able to get into his database. Well, after a careful review, we realize that one of his co-workers was pulling the prank on him. He had given additional permission to his guest user which leads any user logging into server got admin rights to my friend’s database.

Upon figuring out this issue, we disabled the guest account and the problem was resolved. To complete the story, the guy who played the prank on my friend was moved to another department (almost as good as fired) where he could not touch the production database.

Reproduction of the scenario

After remembering the story, I decided to recreate the scenario here. Please note: do not play with Guest Account without fully understanding its implications.

USE AdventureWorks
GO
-- Note: NEVER run this on production server
-- Enabling guest account
GRANT CONNECT TO guest
GO
ALTER ROLE [db_owner] ADD MEMBER [guest]
GO
-- create test login
USE MASTER
GO
CREATE LOGIN [test] WITH PASSWORD=N'Complex@1212';
GO

When you explore the logins of the AdventureWorks database, you will not see ‘test’ as a user. Now let us login into SQL Server using ‘test’ user. When user will login using ‘test’ user he will be logged in as guest user in AdventureWorks database. Every user who login to AdventureWorks as guest user it will have admin access to the database.

This was the precise situation my friend had and he was not able to understand where things went wrong. As I said earlier, unless there is special case, guest user should be disabled.

Run following script to drop ‘test’ account which we created.

USE MASTER
GO
DROP LOGIN [test]
GO

Script to disable the guest account

REVOKE CONNECT FROM guest
GO

Well, share your stories in comments area. Additionally, have you ever prank your friend, if yes, I want to know. Additionally, for master, msdb and tempdb database guest user is required for SQL Server. If you enable guest database on modeldb, all the subsequent created database will have it enable, which is serious security issue.

NOTE: Do not try any of the script on production server or live environment. These scripts are for better understanding of the subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Case Sensitive Database and Database User – Fix: Error: 15151 – Cannot find the user , because it does not exist or you do not have permission.

Jeff asked me another question!

If you do not know Jeff, you may read the following blog posts. You will get the idea of Jeff’s personality and who Jeff really is.

This time, he sent me a screenshot. He was facing a very strange error. As his screenshot had confidential details, I created my own images which exactly simulate his issue for demonstration’s sake. Here are the partial details of his email. Please note that I have replaced his username and database name to protect his private information.

“Hi Pinal,

I think I am having one of those moments when I am feeling stupid or SQL Server is having fun with me.

Here is the scenario. I have two databases: AdventureWorks and MyAdventureWorks. I have a user called SQLAuthority in both  databases. Now when I try to grand access the user in one database it works fine, but when I try to do the same thing in another database, it gives me error. I am stunned as both the users are same. As a matter of fact, I had just created those users recently, and now I am not able to gain necessary permission.

Here is the screenshot where everything works fine:

Here is the screenshot that shows where I am getting crazy and gives following error:

Msg 15151, Level 16, State 1, Line 2
Cannot find the user ‘sqlauthority’, because it does not exist or you do not have permission.

Help me again. Why is this happening? I do not get it. HELP!”

That’s a very interesting question from him. For a moment, I was speechless as it is difficult for me to solve the problem for him remotely. After a while, I noticed that in his example, the case of the username did not match. In SSMS the username was SQLAuthority and in his T-SQL script it was sqlauthority. I told him to change the case of the T-SQL to match with the case of SSMS, and it right away solved his problem.

I asked him to run the following script which validated that his database was throwing an error because of case sensitivity. Read here how you can identify collation of any database: SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') AdventureWorks;
SELECT DATABASEPROPERTYEX('MyAdventureWorks', 'Collation') MyAdventureWorks;

The above query returns the following results:

Learning: Case sensitivity of a database does not only matter in the data of the table, but also in objects like tablename, columnname, and even username. If you know any other situation like this, please share your knowledge in the comments area.

Reference: Pinal Dave (http://blog.sqlauthority.com)