SQL Server 2008 New Security Features:

Compared to SQL 2005 we have got pretty good new security features in the latest version SQL 2008. To list out the new features goes like this:

1. Policy Based Management.
2. Transparent Data Encryption.
3. Upgraded Auditing features.
4. Employ Extensible Key Management.

To begin with ‘Policy Based Management’ is one of the new features of SQL 2008. This feature allows defining and enforcing different policies for managing and configuring SQL Server. For example, if we want to enforce a policy which will make sure the DB Options to be set as per standard like ‘Auto Create/Update Statistics’ options should be Turned On or to enforce a policy which checks for naming conventions for all ‘Views’ to start with ‘VW-‘. This feature is available in all editions of SQL Server 2008.

Before going to start how to enforce policies we need to look at set of new terms which are related to

1.Policy-based Management:

1. Policy: It is a condition to be checked and/or enforced.
2. Condition: It is a property expression that evaluates to True or False which gives the state of a Facet.
3. Facet: These are the predefined set of properties that can be managed.
4. Target: It’s an entity that is managed by Policy – Based Management like Database, Table, etc..,
A ‘Target’ will be managed using a set of Pre-defined ‘Facets’ depending on some ‘Condition’ through a ‘Policy’ which enforces the condition to be checked.

2. Transparent Data Encryption:

The next new security feature is TDE (Transparent Data Encryption) this feature allows encryption of data and log files. Data is encrypted before it is written to disk and it’s decrypted when it is read from disk. This feature helps in encrypting backup files as well and these backup files are helpful in restore only if the Certificates are also backed up. This feature is available only in Enterprise Edition of SQL Server 2008.
Following are the things we need to be aware before encrypting any database or its backups:

1. Master Key: A master key is a symmetric key that is used to create certificates and asymmetric keys.
2. Certificate: Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
3. Database Encryption Key: A database encryption key is required for TDE.

As a first step, we need to create a ‘Master Key’ before enabling TDE or using already encrypted Backup/Data/Log files.

Creation of Master Key:

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘P@ssw0rd’;
GO

Creation of Certificate:

We can create a Certificate as shown below

USE master;
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = ‘TDE Certificate’
GO

Creation of Database Encryption Key:

We can create Database Encryption Key as given below:

USE AdventureWorks;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestCertificate
GO

Enabling TDE:

The below command helps you to enable TDE on any DB:
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO

Note: After creation of Master Key and Certificate they need to be backed up as these are mandatory if we want to re-use the encrypted backup or Data/Log files.

Backup Certificates:

Backup the certificate/Master Key using the below commands:

BACKUP CERTIFICATE DEKCertificate TO FILE = ‘C:\DEKCertificate.cert’
WITH PRIVATE KEY
(
FILE = ‘C:\Instance1PrivateKey.key’,
ENCRYPTION BY PASSWORD = ‘P@ssw0rd’
)

While restoring or using the encrypted files first the Master Key and Certificates needs to be created in the new instance of SQL Server 2008 then only it will allow us to use these files.

Creation of Certificate from Backup:

CREATE CERTIFICATE DEKCertificate
FROM FILE = ‘C:\DEKCertificate.cert’
WITH PRIVATE KEY (
FILE = ‘C:\Instance1PrivateKey.key’
, DECRYPTION BY PASSWORD = ‘P@ssw0rd’
)

Without backups of these keys, it won’t allow us to restore the database using encrypted backup files.

3. Upgraded Auditing features:

This new security feature allows you to monitor any Server level or Database level activities. This can be achieved by using a separate tool called SQL Server Profiler in SQL Server 2005 but this tool has some limitations in providing granular information like ‘who accessed the data’, for configuring/managing traces requires always we require this separate tool profiler and this tool is not accessible via management studio(SSMS).

SQL Server 2008 Audit can automatically log changes made to databases and database server settings and allows administrators to audit a wider range of information and settings than was possible in previous versions, without requiring any custom code or third-party tools. Most notably, administrators can now log changes made to database tables and schema. SQL Server auditing is available only in Enterprise Edition.

Another advantage compared to SQL Server Trace is SQL Server Audit can log events in a binary file that can be viewed using Management Studio, the Windows Application event log, or the Windows Security event log. SQL Server 2005 Trace logs events in a binary file format which require a separate tool called SQL Server Profiler, for viewing.

Now we will create a sample Audit which will trace all successful and failure logins of a server. For this first we will need to create server level Audit as shown below:

Creation of Server Level Audit:

CREATE SERVER AUDIT Audit_Logins TO
File (FILEPATH=’c:\TEMP’, MAXSIZE=100 MB)
WITH (QUEUE_DELAY=2000, ON_FAILURE=SHUTDOWN)GO

Creation of Audit Specification:
Audit specification specifies what sort of actions to be audited:
CREATE SERVER AUDIT_SPECIFICATION Audit_Logins_Specification
FOR SERVER AUDIT Audit_Logins
ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP)
GO
Enabling Audits/Audit Specifications:

By default the Audits and Audit Specifications created will be in disabled state we can enable by using below commands:

ALTER SERVER AUDIT Audit_Logins
WITH (STATE=ON)
GO

ALTER SERVER AUDIT SPECIFICATION Audit_Logins_Specifications
WITH (STATE=ON)
GO

To View Audit File:

And in order to view the Audit file we can use the below query:

Select * from sys.fn_get_audit_file (‘C: \filename.sqlaudit’, default, default)

The ‘sys.fn_get_audit_file‘ is an in-line function which requires 3 parameters to be passed. The first parameter is the @file_pattern which we need to provide the file location and the remaining 2 parameters @initial_file_name and @audit_record_offset which we can pass default values.
Disabling the Audit/Specification disables auditing.

4. Extensible Key Management:

One of the new features with SQL Server 2008 is the addition of extensible key management (EKM), allowing hardware devices to be integrated into the encryption/decryption process for the keys securing your data. This feature is available in Enterprise Edition of SQL Server 2008.
Essentially you are registering your device within SQL Server as a way to handle cryptographic functions, kind of like you used to register DLLs so you could implement an extended stored procedure. In this case, there is a new CREATE statement, the CREATE CRYPTOGRAPHIC PROVIDER which registers the DLL for your EKM system. This is provided by the manufacturer and doesn’t get you access to the device. There will be a device specific procedure for logging into the device. Once that is complete, you’ll have to set up a credential and grant a login rights to the credentials for other individual logins to use the HSM device.

There are very few vendors currently who are providing these devices like SafeNetLuna HSM and so on…

Conclusion:

After all SQL Server 2008 is having many new security features which help in providing effective management of security configuration, strong access control, enhanced Auditing and powerful encryption and key management capabilities.

Links:

http://www.microsoft.com/sqlserver/2008/en/us/security.aspx

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-security.aspx

http://searchsqlserver.techtarget.com/tip/New-security-features-in-SQL-Server-2008-leave-some-work-for-you

http://www.databasejournal.com/features/mssql/article.php/3899866/Top-10-SQL-Server-2008-Security-Features.htm

 

Locks of SQL Server:

In this article, I would like to discuss about kinds of SQL Server locks, and how compatible among them. For ACID implementation, SQL Server uses locks. So lock is a very important function in a multi-user database system. The lock is used in concurrency model. In SQL Server 2005 and next version, there are 2 types of concurrency model, pessimistic and optimistic. There are 12 kinds of locks. Let’s see each of them.

Shared lock (S):
This lock allows the process to read resource but not allows other processes to modify the resource.

Exclusive lock (IX):This lock allows a process to modify resource but doesn’t allow other processes either to read or to modify the locked resource.

Update lock (U:
This lock prevents another process to update or exclusive lock. This lock is used when searching data to modify.

Intent Share lock (IS):
This lock indicates a component of the resource has shared lock. This lock exists on page or table level.

Intent Update (IU):
This lock indicates a component of the resource has update lock. This lock exists on page or table level.

Intent Exclusive (IX):
This lock indicates a component of the resource has an exclusive lock. This lock exists on page or table level.

Shared with Intent Exclusive (SIX):
This lock indicates a resource that holds shared lock contains component (row or page level) holding an exclusive lock. This lock exists at row or page level.

Shared with Intent Update (SIU):
This lock indicates a resource that holds shared lock contains component (row or page level) holding update lock. This lock exists at row or page level.

Update with Intent Exclusive (UIX):
This lock indicates a resource that holds update lock contains component (row or page level) holding an exclusive lock. This lock exists at row or page level.

Schema Stability (Sch-S):
This lock indicates a query using this table is being compiled.

Schema Modification (Sch-M):
This lock indicates a table being changed for its structure.

Bulk Update (BU):
This lock exists when there is Bulk Insert operation into a table and TABLOCK hint is also applied.Understanding behavior of these locks will help you out troubleshooting locking issue.

Links:

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

http://support.microsoft.com/kb/271509

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

How to avoid SQL Injection:

What is SQL Injection?

An attack technique used to exploit web sites by altering backend SQL statements through manipulating application input

SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn’t properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it. Certain SQL Servers such as Microsoft SQL Server contain Stored and Extended Procedures (database server functions). If an attacker can obtain access to these Procedures it may be possible to compromise the entire machine. Attackers commonly insert single qoutes into a URL’s query string, or into a forms input field to test for SQL Injection. If an attacker receives an error message like the one below there is a good chance that the application is vulnerable to SQL Injection

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e14′
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword ‘or’.
/wasc.asp, line 69

What is Blind SQL Injection?

When an attacker executes SQL Injection attacks sometimes the server responds with error messages from the database server complaining that the SQL Query’s syntax is incorrect. Blind SQL injection is identical to normal SQL Injection except that when an attacker attempts to exploit an application rather then getting a useful error message they get a generic page specified by the developer instead. This makes exploiting a potential SQL Injection attack more difficult but not impossible. An attacker can still steal data by asking a series of True and False questions through sql statements.

Preventing SQL injection

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered.

Enforcement at the database level

Currently only the H2 Database Engine supports the ability to enforce query parameterization. However, one drawback is that query by example may not be possible or practical because it’s difficult to implement query by example using parametrized queries.

Enforcement at the coding level

Using object-relational mapping libraries avoids the need to write SQL code. The ORM library in effect will generate parameterized SQL statements from object-oriented code.

Links:

http://en.wikipedia.org/wiki/SQL_injection#Preventing_SQL_injection

http://www.sqlmag.com/article/tsql3/how-to-avoid-sql-injection.aspx

http://msdn.microsoft.com/en-us/library/ms161953.aspx

‘Guest’ user account in SQL Server 2000/2005:

This user account is used by SQL Server to access objects across databases if explicit rights are not granted. This account is relied on for some SQL Server 2000 system functions (master and tempdb) and needs to be maintained for SQL Server to function properly. Unfortunately, in some respects the guest user account can be a considered a threat from an application perspective that can be resolved in user defined databases.

SQL Server 2000 – The guest user account existed in all of the databases including new user defined databases because the user existed in the Model database. This user account is mandatory for the master and tempdb databases for SQL Server to operate properly and should never be removed from these databases. The need in the remainder of the user defined databases is application dependent and MUST be tested thoroughly.

SQL Server 2005 – With the introduction of the schema, database users are separated from objects providing an additional layer of security over SQL Server 2000. Although the guest user remains in all of the user and system databases performing the same general functionality in the SQL Server 2000 world it, the guest user can have its CONNECT permissions revoked.

Please refer the scripts below to determine if the guest user account exists in each of your databases:

SQL Server 2000 and SQL Server 2005

EXEC sp_MSforeachdb ‘USE [?];

SELECT * FROM sysusers;’

GO

Remoce/Disable Guest user account:

Once you have thoroughly tested your applications, the following scripts can be used to remove/disable the guest user account:

SQL Server 2000 SQL Server 2005

USE

GO

EXEC sp_revokedbaccess ‘guest’

GO USE

GO

REVOKE CONNECT FROM GUEST

GO

Source: SQL Server Books online

 

Differences between SQL Server 2005 and SQL Server 2008

Hi,

Sharing few links that I came across today while browsing. These links are having detailed information about the differences with older version of SQL Server and features of SQL Server 2008. Hope this will be useful to all:

http://www.databasejournal.com/features/mssql/article.php/3691821/Whats-new-in-SQL-2008-Part-1.htm

http://www.databasejournal.com/features/mssql/article.php/3697056/Whats-new-in-SQL-2008-Part-2.htm

http://www.databasejournal.com/features/mssql/article.php/3702381/Whats-New-in-SQL-Server-2008-Part-3.htm

http://www.microsoft.com/sqlserver/2008/en/us/

http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx

How to resolve database suspect mode issue?

A database will be marked suspect for one of the following reasons:

• If one or more database files are not available.
• If the entire disk where database files are residing is not available.
• If one or more database files are corrupted.
• If a database resource is being held by the operating system.

 We can try any of the suitable below resolution steps to bring back the DB online.

Method:1

USE Master
GO

– Determine the original database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO

– Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 24
WHERE [Name] = ‘YourDatabaseName’
GO

– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO

– Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

Method:2

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Method:3

use master

sp_detach_db ‘mydb’

use master

sp_attach_db ‘mydb’,'E:\Sqldata\mydbdata.mdf’,'E:\Sqldata\mydblog.ldf’

In this method, we can detach the Db from the server either by using the above command or through GUI. After detaching the DB we need to attach.

While attaching if the log file of the DB is very huge and inaccessible it will throw errors and will not allow us to attach it. In such don’t select the log file only select the ‘.mdf’ file and SQL server will assume that there is no log for the Db and it will create/rebuild a new log.

Method:4

Check if any of the SQL Jobs are accessing the DB this will cause the log to grow and even stops us from detach/attach of DB. Find the SPID and kill it if its not critical then it might allow to proceed accordingly.

After bringing back the DB online by using any method above always run below DBCC commands to make sure everything is back to normal.

DBCC CHECKDB – Validate the overall database integrity

DBCC CHECKCATALOG – Validate the system catalog integrity

DBCC CHECKTABLE – Validate the integrity for a single table

Useful Links:

http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx

http://support.microsoft.com/kb/180500


Thanks VV!!

Windows Explorer keyboard shortcuts

ALT+- (ALT+hyphen) Displays the Multiple Document Interface (MDI) child window’s System menu.
ALT+ENTER View properties for the selected item.
ALT+ESC Cycle through items in the order they were opened.
ALT+F4 Close the active item, or quit the active program.
ALT+SPACEBAR Display the System menu for the active window.
ALT+TAB Switch between open items.
ALT+Underlined letter in a menu name Display the corresponding menu.
BACKSPACE View the folder one level up in My Computer or Windows Explorer.
CTRL+A Select all.
CTRL+B Bold
CTRL+C Copy.
CTRL+I Italics
CTRL+O Open an item.
CTRL+U Underline
CTRL+V Paste.
CTRL+X Cut.
CTRL+Z Undo.
CTRL+F4 Close the active document in programs that allow you to have multiple documents open simultaneously.
CTRL while dragging an item Copy selected item.
CTRL+SHIFT while dragging an item Create shortcut to selected item.
CTRL+RIGHT ARROW Move the insertion point to the beginning of the next word.
CTRL+LEFT ARROW Move the insertion point to the beginning of the previous word.
CTRL+DOWN ARROW Move the insertion point to the beginning of the next paragraph.
CTRL+UP ARROW Move the insertion point to the beginning of the previous paragraph.
CTRL+SHIFT with any of the arrow keys Highlight a block of text.
DELETE Delete.
SHIFT+DELETE Delete selected item permanently without placing the item in the Recycle Bin.
ESC Cancel the current task.
F1 Displays Help
F2 Rename selected item.
F3 Search for a file or folder.
F4 Display the Address bar list in My Computer or Windows Explorer.
F5 Refresh the active window.
F6 Cycle through screen elements in a window or on the desktop.
F10 Activate the menu bar in the active program.
SHIFT+F10 Display the shortcut menu for the selected item.
CTRL+ESC Display the Start menu.
SHIFT+CTRL+ESC Launches Task Manager
Underlined letter in a command name on an open menu Carry out the corresponding command.
RIGHT ARROW Open the next menu to the right, or open a submenu.
LEFT ARROW Open the next menu to the left, or close a submenu.
SHIFT with any of the arrow keys Select more than one item in a window or on the desktop, or select text within a document.
SHIFT when you insert a CD into the CD-ROM drive Prevent the CD from automatically playing.
Windows Explorer keyboard shortcuts
Shortcut Keys Function
CTRL+PLUS
on numeric keypad (+) Automatically resize the columns in the right hand pane
END Display the bottom of the active window.
HOME Display the top of the active window.
NUM LOCK+ASTERISK
on numeric keypad (*) Display all subfolders under the selected folder.
NUM LOCK+PLUS SIGN
on numeric keypad (+) Display the contents of the selected folder.
NUM LOCK+MINUS SIGN
on numeric keypad (-) Collapse the selected folder.
LEFT ARROW Collapse current selection if it’s expanded, or select parent folder.
RIGHT ARROW Display current selection if it’s collapsed, or select first subfolder