Hello All, We are going to start new batch from next week. message/call or mail us for more details.

4 September 2013

Beginning of SQL Server Security

Security! This is the word comes in mind of every concerned person when it comes to store, access and share the data and database or database server. At times when applications are run in the geographically restricted area, there were less chance of its exploitation and with spread of internet and the availability of application over the internet; security of data, databases and database servers has become vital.


SQL Server Security has been so much important for every organization or institute or company or government has their data that is vital to them, and not to be accessed by the authorized person or entity only. And that is vital to set some security standards for such a critical data so that you can prevent your data as much as you can with proper security mechanism and set of standards within organization and in your scope for MS SQL Server Security.

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.



Securable 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 securable.


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:


Create Login SqlLogin with password='password@123'


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


Create Login [<DomainName>/<UserName>] From Windows


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


Beginning of SQL Server Security – Guest Post


Fill the form to create a new login


Beginning of SQL Server Security – Guest Post

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 User UserLogin From Login SqlLogin


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


Beginning of SQL Server Security – Guest Post


Fill the form


Beginning of SQL Server Security – Guest Post

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:


EXEC sp_addsrvrolember  SqlLogin, ServerAdmin


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


EXEC sp_addsrvrolember 'db_datareader','SqlUser

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:
 

Beginning of SQL Server Security – Guest Post


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.

Upgrade SQL Server 2005 SP3 to SP4

To download SP4, go to this link:


If you are looking for the Express versions of SP4, you can get Express, Express with Tools, and Express with Advanced Services at the following URL (though they don't really do a great job of describing which file(s) may be most appropriate for you):


If you want Express that can install on 32-bit or 64-bit systems, download SQLEXPR.EXE.  If you know you will only install to 32-bit systems, download the smaller SQLEXPR32.EXE.  (The only difference is the size of the package - I can hear the peanut gallery now.  In all seriousness, there is no such thing as a 64-bit version of SQL Server Express for SQL Server 2005 - which option first arrived with SQL Server 2008.)  If you want Express with Tools, use SQLEXPR_TOOLKIT.EXE, and if you want Express with Advanced Services, use SQLEXPR_ADV.EXE.

List of the issues that are fixed in SQL Server 2005 Service Pack 4

 
This article contains information about the issues that are fixed in Microsoft SQL Server 2005 Service Pack 4 (SP4).
Notes
·         Other fixes that are not documented may be included in the service pack.
·         This list will be updated when more articles are released.

Issues that are fixed in this service pack

 
For more information about the issues that are fixed in SQL Server 2005 Service Pack 4, click the following article numbers to view the articles in the Microsoft Knowledge Base: 

VSTS bug number
KB article number
Description
294192
FIX: 0xC02020A1, 0xC020902A, 0xC0202092, and 0xC0047038 errors may occur at the same time when you try to run an SSIS 2005 package if you use multi-character column delimiters in a Flat File source
396531
Correcting the lack of cryptographic salt variation on SQL Server sa login hash
308002
SQL Server 2005 setup fails when MSXML Core Services 6.0 Service Pack 2 has already been installed

Additional issues that are fixed in this service pack

Resolutions to the following issues are also included in SQL Server 2005 Service Pack 4:
VSTS bug number
Description
297747
The multimedia timer frequency does not increase when you use Windows Internal Database.
555409
An access violation error may occur during the execution of Extended Stored Procedures under high stress conditions.


 
In addition to the fixes that are listed below, SQL Server 2005 SP4 contains the hotfixes that were included in cumulative update packages for SQL Server 2005 Service Pack 3 from cumulative update package 1 to cumulative update package 11.
For more information about the cumulative update packages for SQL Server 2005 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base: 

960598 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released.

How to Backup a Table in SQL Server

There is not standard method for backing up a table in SQL Server.  A common request I receive from clients is to only backup specific tables within a database.  Well, unfortunately there is no out of the box method for doing this akin to a database backup so we need to roll our own way.

It is usually done one of two ways.  The first is to use a select into method to copy the table.  The other way is to script the table out using the Generate Scripts Tasks.  Both methods have positives and negatives.  First off, the SELECT INTO Method is by far the fastest.  It can copy a large number of rows very quickly, the downfall to this however, is that it does not carry over the Keys, Indexes or Constraints.  The Generate Scripts method is slow (and I don’t recommend it for very large tables), however it can facilitate copying over any of the other objects associated with the table.  Let’s look at the generate scripts task:

First, right click on the database that contains the table you want to backup and choose Tasks -> Generate Scripts.

How to Backup a Table in SQL Server

The Generate Scripts Wizard Appears.  Select “Next” past the splash screen then select the database that contains the table.  The next screen that appears 
is the Script Options.

How to Backup a Table in SQL Server

Scroll down on the script options until you see Table/View Options.  We want the following to be true: Check Constraints, Script Data, Foreign Keys, Primary Keys, Triggers, & Unique Keys.  (Or you can choose whatever you need of course).  Select Next and we are presented with the Select Object Types Screen.

How to Backup a Table in SQL Server

Select Tables and hit next.  Lastly, choose the table or tables you want to backup and hit next.
 
How to Backup a Table in SQL Server

The last dialog provides the medium you want to output the script to.  If you are backing up a large table, I suggest you output to a file.

Change Data Capture (CDC) in SQL Server

Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor.

Under the hood, CDC is written using the same repl logreader function that transactional replication uses. Don’t worry too much though. If you are like me, then when you hear the word replication, you start running for the hills. Well, CDC is not as much maintenance. The biggest thing you have to worry about with CDC, is that the disk that contains the log never gets full. When that does happen, and it should be rarely, then yes it is a pain. What you have to do in that case is flip the do not truncate bit for the log file to ‘no’. That aside, let’s get started.

First thing you want to do is enable change data capture. This is done at the database level.

USE AdventureWorks
GO
 
DECLARE @ReturnCode int
 
EXEC @ReturnCode = sys.sp_cdc_enable_db
 
SELECT @ReturnCode
 
SELECT
    name
    ,is_cdc_enabled
FROM sys.databases

 



Change Data Capture (CDC) in SQL Server


Even though we performed the above action, nothing will happen until we define the tables and columns that it should monitor. When defining a table to monitor, there are a few parameters that need to be passed:

                                                              CDC Parameters

Parameter

Description

@source_schema
The schema name of the table to monitor (ex: dbo)
@source_name
The name of the table to monitor
@role_name
A database or server role which is used to grant access to the data. If the specified role does not exist, it will be created. Note: DB Owner role can always access the data
@supports_net_changes
When enabled you are able to retrieve all changed values within a single row for a given time period (using LSN’s – Log Sequence Numbers). Otherwise, multiple rows are returned.
@captured_column_list
List of columns to capture. Must either include primary key, or specify a unique index using the @index_name parameter
@filegroup_name
Name of the filegroup to store the Change Data on

 

Let’s enable a table to run CDC:

EXEC sys.sp_cdc_enable_table
@source_schema          = 'Sales'       --mandatory
, @source_name          = 'Customer'    --mandatory
, @role_name            = 'cdc_manager' --mandatory
, @supports_net_changes = 0
, @captured_column_list = 'CustomerID,CustomerType,TerritoryID'
, @filegroup_name       = N'PRIMARY';

 

Once we run the above sample, we’ll notice there are two new jobs running under the SQL Server Agent.


Change Data Capture (CDC) in SQL Server


The first job, cdc.AdventureWorks_capture, is not much different than a replication job. Basically this job runs the repl log reader by executing it in an infinite loop. The next job, is the cleanup job which prunes the data by only retaining two days of data. (This is configurable however).

Now that we’ve enable CDC for the Sales.Customer table, let’s update some records to see how CDC works. 
 

-- Update some records
UPDATE TOP (1) Sales.Customer
SET CustomerType = 'S'
WHERE CustomerType != 'S'
 
-- Query the capture table
SELECT *
FROM cdc.Sales_Customer_CT

 

Change Data Capture (CDC) in SQL Server


From the capture table output, we see two records. One record represents the original value (update mask = 3), while the second record represents the new changed value (update mask = 4).

Here are the update mask values:

·         1 = delete

·         2 = insert

·         3 = update (old values)

·         4 = update (new values)

In our next article we will go into depth in querying CDC and the internals.

Speech Bubble Shapes in CSS

#bubble-1
{
    background:red;
    width:300px;
    height:50px;
    position:relative;
    border-radius:10px;
    -moz-border-radius:10px;
    -webkit-border-radius:10px;
    left:40px;
}
#bubble-1:after
{
    position:absolute;
    content:"";
    width:0;
    height:0;
    border-top:10px solid transparent;
    border-bottom:10px solid transparent;
    border-right:40px solid red;
    right:100%;
    top:20px;     
}
 
Speech Bubble Shapes in CSS
#bubble-2
{
    background:red;
    width:300px;
    height:50px;
    position:relative;
    border-radius:10px;
    -moz-boredr-radius:10px;
    -webkit-border-radius:10px;
}
#bubble-2:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-top:10px solid transparent;
    border-bottom:10px solid transparent;
    border-left:40px solid red;
    left:100%;
    top:20px;
}
 
Speech Bubble Shapes in CSS
#bubble-3
{
    width:300px;
    height:50px;
    background:red;
    position:relative;
    border-radius:10px;
    -moz-boredr-radius:10px;
    -webkit-border-radius:10px;
}
#bubble-3:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-left:10px solid transparent;
    border-right:10px solid transparent;
    border-bottom:20px solid red;
    left:80%;
    top:-20px;
}
 
Speech Bubble Shapes in CSS
#bubble-4
{
    width:300px;
    height:50px;
    background:red;
    position:relative;
    border-radius:10px;
    -moz-boredr-radius:10px;
    -webkit-border-radius:10px;
}
#bubble-4:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-left:10px solid transparent;
    border-right:10px solid transparent;
    border-top:20px solid red;
    right:80%;
    top:50px;
}
 
Speech Bubble Shapes in CSS
#bubble-5
{
    width:300px;
    height:50px;
    background:blue;
    position:relative;
    border-radius:10px;
    -moz-boder-radius:10px;
    -webkit-border-radius:10px;
}
#bubble-5:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-top:20px solid blue;
    border-right:20px solid transparent;
    top:50px;
    right:80%
}
 
Speech Bubble Shapes in CSS
#bubble-6
{
    width:300px;
    height:50px;
    background:blue;
    position:relative;
    border-radius:10px;
    -moz-boder-radius:10px;
    -webkit-border-radius:10px;
}
#bubble-6:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-bottom:20px solid blue;
    border-left:20px solid transparent;
    top:-20px;
    left:80%;
}
 
Speech Bubble Shapes in CSS
#bubble-7
{
    width:300px;
    height:50px;
    background:blue;
    position:relative;
    border-radius:10px;
    -moz-boder-radius:10px;
    -webkit-border-radius:10px;
}
#bubble-7:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-bottom:15px solid blue;
    border-right:40px solid transparent;
    left:100%;
    top:10px;
}
 
Speech Bubble Shapes in CSS
#bubble-8
{
    width:300px;
    height:50px;
    background:blue;
    position:relative;
    border-radius:10px;
    -moz-boder-radius:10px;
    -webkit-border-radius:10px;
    left:60px;
}
#bubble-8:after
{
    content:"";
    position:absolute;
    width:0;
    height:0;
    border-bottom:15px solid blue;
    border-left:40px solid transparent;
    right:100%;
    top:10px;
}
 
Speech Bubble Shapes in CSS
#bubble-9
{
    width:300px;
    height:50px;
    border:5px solid #5a8f00;
    border-radius:10px ;
    -moz-border-radius:10px;
    -webkit-border-radius:10px;
    position:relative;
}
#bubble-9:before
{
    content:"";
    position:absolute;
    bottom:-20px; /* value = - border-top-width - border-bottom-width */
    left:40px; /* controls horizontal position */
    border-width:20px 20px 0;
    border-style:solid;
    border-color:#5a8f00 transparent;
    /* reduce the damage in FF3.0 */
    display:block;
    width:0;
}
#bubble-9:after
{
       content:"";
       position:absolute;
       bottom:-13px; /* value = - border-top-width - border-bottom-width */
       left:47px; /* value = (:before left) + (:before border-left) - (:after border-left) */
       border-width:13px 13px 0;
       border-style:solid;
       border-color:#fff transparent;
    /* reduce the damage in FF3.0 */
    display:block;
    width:0;
}
 
Speech Bubble Shapes in CSS
#bubble-10
{
     width:300px;
    height:50px;
    border:5px solid #5a8f00;
    border-radius:10px ;
    -moz-border-radius:10px;
    -webkit-border-radius:10px;
    position:relative;
}
#bubble-10:before
{
    content:"";
    position:absolute;
    top:-20px; /* value = - border-top-width - border-bottom-width */
    bottom:auto;
    left:auto;
    right:40px; /* controls horizontal position */
    border-width:0 20px 20px;
    border-style:solid;
    border-color:#5a8f00 transparent;
    display:block;
    width:0;
}
#bubble-10:after
{
    content:"";
    position:absolute;
    top:-13px; /* value = - border-top-width - border-bottom-width */
    bottom:auto;
    left:auto;
    right:47px; /* value = (:before right) + (:before border-right) - (:after border-right) */
    border-width:0 13px 13px;
    border-style:solid;
    border-color:#fff transparent;
    display:block;
    width:0;
}