SQL SERVER – Beginning with SQL Server Security Aspects

SQL Server Security Aspects

This article is the high-level overview of the SQL Server security best practices and aspects. Ensuring SQL Server security is an important task that may be successfully solved by applying best practices and proven solutions described further in this article.

Physical Security

SQL Server physical security aspects are often overlooked. Frequently DB admins focus on the software security and network security, while completely forgetting about the physical server itself. However, the possibility to physically reach the server, will lead to abrogation of all other security configurations that they apply to the software. As far as SQL Server is installed on a physical device, whether it would be server hardware, desktop PC, laptop, or other PC in a data center. The physical security main goal is to prevent someone from gaining access to the machine.

Shared data centers or server rooms provide a wide range of physical and environmental security to ensure that secure areas are protected by appropriate entry controls to ensure that only authorized personnel are allowed access. An appropriate, physical protection should be provided against damage from natural, or man-made disasters, such as fire, flood, explosion etc. All users are required to ensure that systems are not left open to access by intruders to buildings, or by unauthorized colleagues.

If the personnel in the data center  is unable to physically lock away the server, you must ensure that all USB ports on the server are disabled. Keep in mind that security is not complete if physical security is overlooked.

Network  Security

The network is the way that external threads may come to attack your SQL Server.

Firewalls provide efficient means to implement security. A firewall is a separator or restrictor of network traffic, which can be configured to enforce your data security policy. If you use a firewall, you will increase security at the operating system.

SQL Server Instance Security

Every SQL Server instance that is installed on a PC can be considered to be a security domain. Thus, at the instance level you could apply separate security considerations.

I will use the dbForge Studio for SQL Server v 4.0 for the demonstration purposes as it has an excellent tool called Security Manager.

The following picture demonstrates two separate instances of SQL Server on the same computer.  Each has its own specific name, the first one is MSSQLSERVER\Instance1, the second one is MSSQLSERVER\Instance2.

This picture gives us the ability to see how security applies at the instance level. SQL Server Security uses the concept of securables that will be discussed later in this article..

Securables are the objects to which the SQL Server authorization system controls access. For instance, a table is a securable. Some securables can be contained within others, creating nested hierarchies called “scopes”. The securable scopes are server, database, and schema.

An object that receives permission to a securable is called a principal. The most common principals are logins and users.

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login).

To control access to securables, a DB admin can grant or deny permissions, or add logins and users to roles that have access.

It is important to note that every individual instance has it’s own set of security considerations. The security folders on each instance contain the same users, application roles,(etc.) folders. However, if we expand the logins folder you can see the different number of accounts.

This proves that we can have different security settings for individual users across different instances.

 It is also important to note that when you do set up an SQL Server instance, you only install the necessary features that are necessary for that instance to operate. This reduces the possibility to attack of your specific SQL Server instance, by reducing the number of services and features that are available for malicious users to gain access to.

SQL Server Securables

Securables are the resources to which the SQL Server Database Engine authorization system regulates access. Essentially these are the database objects to which you can apply security on your instance of SQL Server. Some securables are standalone and others may be contained within another securable.

Each database also has its own security folder, where we can focus on users, which again, become the logins that we assign the permissions to for our database.

We also have database level roles and application roles. Currently, there are no application roles assigned on this server. There are another components that we do not see in the Database Explorer, however they still exist at the data base level. These components are assemblies which are typically DLL files that are used in the instance of sequel server for deploying functions stored procedures, or triggers. These components are managed by CLR.

SQL Server Principals

There are three high level categories of SQL Server security principals:

  1. Windows level (domain and local logins)
  2. SQL Server level (SQL Logins and server roles)
  3. Database level (database users, DB and application roles)

There are two SQL Server authentication types:

  • by using Windows account security token
  • by using an account defined inside SQL Server

You can tune the type of authentication during installation. Windows mode is the most secure, as it relies on Windows accounts as opposed to SQL accounts. The recommended setting is Windows Authentication and it is selected by default. This authentication type  simplifies administration and is more secure, because it uses Windows security tokens. There is no need to remember another password, and no password transits through the local network.

You cannot disable Windows authentication, however you can extend it with SQL Server authentication. Thus, in case you need to allow access to the users outside the local network, you should use SQL Server authentication. In this case, logins are defined and managed inside SQL Server. The authentication and password validation is managed by SQL Server also.

There are also default roles created during installation as well. One of which is known as the public role.

SQL Server creates the public role as a way of assigning permissions to users who have accounts on the database, but who do not have any other currently assigned permissions. The public role serves the purpose, of providing limited permissions to user accounts on the database, until you’ve had the opportunity to assign the necessary permissions for those user accounts. SQL Server also includes a guest user account. This account is not a server level account, but exists at the database level.

If we expand the users folder within the database, we will notice a guest account, that is available for each database. The guest account is used to grant permissions to users, who might be able to access the database. But, who do not have a user account assigned, or created in the data base itself.

The guest account cannot be dropped, but it can be disabled, through the revocation of the connect permission. User and application access to your SQL Server instance, will be controlled using these security principles.

Manage Logins and Users

As mentioned above in this article, SQL Server implements security through the use of securables, which are the database objects, and security principles, which are the user’s inner applications that will access the database.

If you know how to create the logins and user accounts – it is the first step in creating the authentication and authorization model for your SQL Server.

Before creating logins, you must know which authentication mode SQL Server instance is configured to use. In case of mixed mode authentication, we can create Windows accounts, or we can create local SQL Server accounts for authentication on the instance of SQL Server.

Keep in mind that SQL Server allows you to create logins through Windows accounts or SQL Server accounts at the server level. Also SQL Server permits the creation of local user accounts at the database level. If we expand the database, and expand the Security folder, we notice that SQL Server includes a Users folder at the database level.

Simply right click the Users folder, and then click New User. This allows us to choose a user type at the database level, as opposed to the server level. We can create SQL user with or without a login, what means that this would be a user name we create specifically in this database.

A good example of such an account is the guest account, which is created at the database level, but does not exist at the logins level for the server itself.

You can set the authentication mode to Mix Mode at any time after the installation. And then you can create SQL Server accounts, or use Windows accounts, depending on your choice.

Understanding each type of account and how security is applied to these accounts will help you set the proper security for user access.

Understanding and using roles

SQL Server roles allow you to group users or logins into a specific container for assigning permissions to securables on your SQL Server instance. Permissions assigned to a role are applied to any user or login that is associated with that role. SQL Server provides nine fixed server roles. You can find these server roles in the Security folder of the SQL Server instance. Outside of the nine fixed server roles, SQL Server also provides database level roles.

SQL Server Permissions

Accessing any of the secureables in SQL Server requires that the login or user have permissions. The permissions in SQL Server follow certain naming conventions. The security check algorithm used by SQL is complex. And the complexity comes from the need to evaluate group or role membership, as well as explicit and implicit permissions.

There are three core elements of SQL Server permission checking:

  1. Security context. This is related to the user, the login or the role.
  2. Permission space. The permission space will focus around the securable, such as the database object that the user is attempting to access. SQL Server checks the permissions that the principle has been assigned according to that securable.
  3. Required permissions. This is an important step because some tasks require more than one permission. For instance, if a user attempts to execute a stored procedure, the execute permission would be required in the stored procedure itself.

Summary

SQL Server security is too huge topic to be discussed in a single article. However, this article provides an overview of core principles and concepts of SQL Server security.

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

About these ads

SQL SERVER – Proof of Concepts with Windows Azure – Notes from the Field #056

[Note from Pinal]: This is a 56th episode of Notes from the Fields series. If you are DBA and Developer there is always a situation when you want to prove yourself by building a small proof of concepts of your idea. However, most of the time, it is way more complicated than we think. Building proof of the concepts required many different resources and skills. Above all there are chances that what we have built is not upto the mark and we have to rebuild the example one more time. Trust me it is never simple those tasks which looks simple from the beginning.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how to build proof of concepts and how to maximize the power of Azure. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


Whether you know it or not cloud services are here and they are changing the way we will provide information technology services. For example, in many information technology shops it can take weeks if not months to get an instance of SQL Server up and running.  Here are some minimal action items that must be completed before DBA’s get access server to install SQL Server.  You have to order a physical server, your procurement team must approve the order, and the server has to be shipped. Once the server is received the server must be racked in the data center, cables must be connected, and the data center team needs to document their changes. Then the operations team needs to install and configure windows. I could keep going but there are a lot of things that should be done to a server before the DBA team gets its hands on it. What are you going to do if you’re a DBA and you need instance up in 30 minutes for a proof of concept? It’s becoming more common that the cloud is the answer.

Every time I need a server for a proof of concept I jump to Windows Azure.  I can quickly build a Windows Azure Machine with SQL Server provided within 30 minutes.  In this tip, I am going to walk through the steps to create your first Windows Azure Machine.

1. Get Azure Windows Account. If you don’t have one you can get a free trial. At the time of writing this tip your free trial would include $220 of credit.
http://azure.microsoft.com/en-us/pricing/free-trial/

Are you an MSDN Subscriber who isn’t leveraging Microsoft’s cloud service? If so, you can sign up for free monthly credit.

2. Log In to Azure Portal
https://manage.windowsazure.com

3. Create New Virtual Machine

On the left hand side, click on Virtual Machines and then the add button on the bottom of the left side of the screen. This will load our wizard for creating our first virtual machine.

Now that wizard is loaded as you can see below we can select virtual machine and create it from the gallery. In the Gallery we will be able to select one of many images used that already includes SQL Server baked in.

Looking at the SQL Server images you will see you can access Enterprise Edition, Standard Edition and Web Edition for SQL 2014 down to SQL 2008 R2.

 

Next you can customize your image by release date. This will allow you to have different service packs or CUs. You can also select between two different tiers and sizes.  You will have to create a user name and password and you will want to keep this credential as it will be your first account.

 

Next you will be able to select more machine configuration options. You will get to determine where the Azure Virtual Machine is located.  Below you will see I am using my MSDN Subscription.

 

Finally, you will get to configure more configuration extensions to help automate or secure your virtual machine.

Finally, you will see your server being provisioned. What once use to take weeks or months can now be done in the cloud in minutes.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

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

SQL SERVER – Inside Temp Table Object Creation

When I wrote the article around tempdb like normal database, I had a number of people asked me how it is so simple to understand? Well, they were asking how are the tables created? How will same tables created by different sessions created inside tempdb? Are there anything different about temp tables when compared to normal tables?

These are all valid questions and sometimes we need to check the output to understand them better. So here is a simple test we are going to do to understand how temp tables are created inside SQL Server. For my example I will create a temp table of the following definition.

-- Session 1: Table creation
CREATE TABLE #temptable_test (
id INT NOT NULL IDENTITY (1,1)
,
Name CHAR(100) NOT NULL
,
DOJ DATETIME NOT NULL
);

After the table is created, we will go ahead and check the object definition created inside tempdb database. We can look at the metadata using:

-- SHOW USER TABLE
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

The important point to note is the negative object id for the table created. Also we need to see that the table created has a long number of underscores and a number (___02). Now why is SQL Server doing this? Why couldn’t SQL Server create the table using the same name we created? The answer is simple. We have a single tempdb for the whole instance and if there are multiple sessions creating the same object, then we don’t want to have conflict in the table created.

To mimic this, let us go ahead and create a second table with the same name but different schema to check what SQL Server does. Our second table definition looks like:

-- Session 2: Table creation
CREATE TABLE #temptable_test (
id INT NOT NULL IDENTITY (1,1)
,
FullName CHAR(100) NOT NULL
);

Though the names are same but the column definitions are different, what we see inside SQL Server tempdb metadata is:

As you can see, the names have different auto number extension making each of these objects unique. This is the reason why SQL Server adds the extra extension. For simplicity sake and as explained in the precious article, the temp table is removed as soon as the session is closed / completed. So let me go ahead to check the same. We will close the session 1 and see the effect.

As you can see, temp tables are similar to normal tables with the exception that the table is disposed as soon as the session is closed.

Do let me know if you have seen this behavior in your environments and how many such objects are available at any point in time in your environments today? Let me know and it will be a great learning about your very own environment.

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

SQL SERVER – Maintenance Plan – Maintenance Cleanup Task not Deleting Files

I am always notified by my blog readers whenever they see something unusual and I try to help them so that I can learn something new. Recently, one of my blog reader told that his drive is getting filled with the regular backups taken via SQL Server Maintenance Plans.

My simple answer was – You should delete or move older backups and Maintenance Cleanup Task is available in the Toolbox. And I was shocked when he said that he already has that task, but it is not working as expected. It is supposed to delete files with extension “bak” which are 7 days old, but its not deleting any of those files. My next question was – is there any error message? The answer was – “When I run the job, it gives me a success message, but older .bak files are still present.”

I asked him to look into the log files generated by maintenance plan. They are generated under LOG folder (which contains SQL ERRORLOGs as well). Here is what was shared.

Microsoft(R) Server Maintenance Utility (Unicode) Version 12.0.2381
Report was generated on “local”.
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
Maintenance Cleanup Task (local)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2014-10-28T10:14:59.
Task end: 2014-10-28T10:14:59.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N”E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup”,N”.bak”,N”2014-09-30T10:14:59”
GO

When I configured the plan on my machine and looked at the log, I got similar output. There is a little difference there. Notice the extension of the file given as .bak (notice a dot before bak). I have highlighted in the output.

Then I asked my friend to share the screenshot of the cleanup task and here is what he shared.

 

And that was the problem. I asked him to keep bak as extension (by removing dot before it) and run the below command.

EXECUTE MASTER.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup',N'bak',N'2014-09-30T10:14:59'
GO

The command is same as what was shown in the log file. I have just replaced two single quotes with one single quote. As soon as that was run, all BAK files older than 2014-09-30T10:14:59 got deleted successfully.

Moral of the story: Doesn’t use dot before the extension of the file in the UI.

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

SQL SERVER – FIX – The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run

The best way one can learn SQL Server is by trying out things on their own and I am no different. I constantly am trying to explore the various options one can use when working with SQL Server. In the same context, when I was playing around with backup restore commands, I made a mistake and unfortunately restarted SQL Server. After that I was unable to start SQL Service.  If I start the service, it doesn’t give any error but gets stop automatically.

Whenever I have any weird problems with SQL, I always look at ERRORLOG files for that instance. If you don’t know the location of Errorlog, you should refer Balmukund’s blog (Help : Where is SQL Server ErrorLog?)

This is what I found in ERROLROG just before the stop.

2014-10-28 002039.02 spid9s      Starting up database 'model'.
2014-10-28 002040.01 spid9s      The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2014-10-28 002040.04 spid9s      Error 927, Severity 14, State 2.
2014-10-28 002040.04 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

The error and behavior which I am seeing makes sense because to start SQL Server, we need master, model and tempdb database. You might think that MSDB is also a system database and would be needed for SQL Engine? Well, you might have been tricked. MSDB is needed for SQL Server Agent Service, not SQL Server Service. So, my master is fine, model has some problem. Every new database is created using model, including TempDB so SQL Service is refusing to start. Since the model database is not recovered successfully, SQL Server cannot create the tempdb database, and the instance of SQL Server does not start understandably.

So I called up Balmukund – these are the perks of having a good friend to rely. He never says “no” but he also doesn’t give complete solution to the problem. He gives hint and asks me to research further. This time also magical words were – “use trace flag 3608 and restore model with recovery”.

I followed his advice and performed below steps.

1. Start SQL Server with trace flag 3608 using net start command

Net Start MSSQL$SQL2014 /T3608

In my case SQL2014 is the name of the instance. If you have default instance then service name would be MSSQLServer. For named instance, it is MSSQL$InstanceNameHere

2. After starting with trace flag 3608, I verified the same from Errorlog as well.

Further, I also found below message in ERRORLOG.

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

3. Connected to SQL Instance using SQLCMD by below command.

SQLCMD -S .\SQL2014 -E

You can read parameter of SQLCMD at Books online here

“1>” means we are connected to SQL Instance and then Executed below command (hit enter at end of each line)

RESTORE DATABASE Model WITH RECOVERY
GO

4. Once the command is executed successfully, we will come back to “1>” again. We can type exit to come out of SQLCMD

5. Now stop SQL Service

Net Stop MSSQL$SQL2014

6. And start again without trace flag.

Net Start MSSQL$SQL2014

Now my SQL instance came up happily and I was unblocked.  After sometime I got call from Balmukund asking if SQL is started and I told that my next blog is ready on the same topic.  He finally asked, how did that happen? And my answer was – I ran wrong command. My command was

BACKUP DATABASE model TO DISK = 'Full.bak'
GO
BACKUP LOG model TO DISK = 'Log.trn' WITH NORECOVERY
GO

My request to my reader is that please DONOT run the above command in your SQL instance and restart SQL else you need to follow the steps in production server. Learning never stops when working with SQL Server.

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

SQL SERVER – SSMS Trick – Generating CSV file using Management Studio

In my previous blog I talked about using SQLCMD to generate a comma separate files for the table data.

Some blog readers reported that why we should use SQLCMD or BCP to generate the file when the same can be achieved using SQL Server Management Studio itself. Well, that was a strong statement and I liked the simplicity of the solution. Here are the steps. A lot of times it is our inability to learn some of the simple techniques present in the tools that we use make us even more productive.

Here are the steps to achieve the same. Go to SQL Server Management Studio > Choose Tools from Menu Bar > Click on Options. Go to “Query Results” > “SQL Server” > Click on “Results to Text”

Once we choose “Comma delimited” and hit OK – we will get below kind of output when we eecure a query (Note that we need to open a new Query Window so that the settings are saved)

 

Notice that first column is the column name. If we want to avoid that and wants ONLY the data, then we can customized that in the same place where we have modified the separator setting. If we uncheck “Include column headers in the result set” then we will get only the rows as output.

We can also choose custom delimiter. I have set delimiter as pipe (|) and unchecked the box which I mentioned earlier and below is the output for the same query.

As we can see that output is pipe separated and we just have data not column heading. This can be saved and imported again to some other server using bcp command, Bulk Insert command or Import/Export Wizard.

Recommended reading:

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

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

It’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)