Month: June 2011

SQLCMD is a command line utility (similar to sqlplus for oracle) that replaces the isql command line utility in SQL2000 and earlier versions of SQL Server

Can use it to make a .bat file that runs several scripts in a particular order, for example:

sqlcmd -S {server} -d {dbname} -i sqlfile1.sqlsqlcmd -S {server} -d {dbname} -i sqlfile2.sql

Sqlcmd
[-U login id] [-P password] [-S server] [-H hostname]
[-E trusted connection] [-d use database name] [-l login timeout]
[-t query timeout] [-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]] [-q “cmdline query”]
[-Q “cmdline query” and exit] [-m errorlevel] [-V severitylevel]
[-W remove trailing spaces] [-u unicode output]
[-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile]
[-f | i:[,o:]]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting] [-b On error batch abort]
[-v var = “value”…]
[-X[1] disable commands[and exit with warning]]
[-? show syntax summary]

After looking at the below STIG entries, my recommendation is that CLR should be disabled on an instance that hosts legacy databases and a separate instance should be maintained for newer databases that use CLR.

While stored procedures remain a preferred way of doing things, in some cases you may need to do something very complex that might be better developed and maintained in managed code.

In this case, CLR would be the preferred solution instead of making calls from within the DBMS out to code that is external to the DBMS environment.

If CLR is required, then this needs to be documented in the System Security Plan that “access to CLR applications is required.”

http://iase.disa.mil/stigs/content_pages/database_security.html

U_INS_sqlserver9_v8r1.7_Checklist_20100827.pdf

STIG ID: DM6123

Use of Command Language Runtime objects should be disabled if not required.

STIG ID: DG0099-SQLServer9

Vulnerability: DBMS’s may spawn additional external processes to execute procedures that are defined in the DBMS, but stored in external host files (external procedures) or to executables that reside on the external host.

Fix: Redesign applications to use CLR integration.

To use encryption and later be able to decrypt data in a column using symmetric encryption involves the following:

1. Service Master Key – unique to each sql server instanced
2. Database Master Key – unique to each database, generated using password and service master key
3. One or more Symmetric Database Certificates
4. One or more Symmetric Encryption Keys that use an algorithm and are tied to a certificate

———————————————————-
— This only needs to be run once after database creation
———————————————————-
USE [database]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyEncryptionPassword’
GO

CREATE CERTIFICATE SSN_CERT_01 WITH SUBJECT = ‘SSN_CERT_01’
GO

CREATE SYMMETRIC KEY SSN_KEY_01
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE SSN_CERT_01
GO

GRANT CONTROL ON CERTIFICATE::[SSN_CERT_01] TO [APPLICATION_USER]
GO

GRANT CONTROL ON SYMMETRIC KEY::[SSN_KEY_01] TO [APPLICATION_USER]
GO

———————————————————-
— This is how to encrypt a value to be inserted into
— column of database table
— This needs to be encapsulated in stored procedures
— that are called from the application, so as not to
— expose encrypt/decrypt process within application code
— NOTE: Will need to ‘OPEN’ the key once per session
———————————————————-

OPEN SYMMETRIC KEY SSN_KEY_01

DECRYPTION BY CERTIFICATE SSN_CERT_01;

UPDATE t_person

SET ssn = EncryptByKey( Key_GUID(‘SSN_KEY_01’), @ssn )

WHERE person_id = @person_id

CLOSE SYMMETRIC KEY SSN_KEY_01

———————————————————-
— This is how to decrypt a value to select from
— column of database table
— This needs to be encapsulated in stored procedures
— that are called from the application, so as not to
— expose encrypt/decrypt process within application code
— NOTE: The cast needs to be the same datatype as the
— data was before it was encrypted
———————————————————-

OPEN SYMMETRIC KEY SSN_KEY_01

DECRYPTION BY CERTIFICATE SSN_CERT_01;

SELECT TOP 1 @ssn = cast( DecryptByKey(A.ssn) as nvarchar(10) )

FROM t_person A WITH (NOLOCK)

WHERE A.person_id = @person_id

CLOSE SYMMETRIC KEY SSN_KEY_01

MOVING FROM INSTANCE TO INSTANCE

By default, a database’s master key is encrypted using the server’s service master key. When you move a database to a new server using backup/restore or detach/attach, the database master key can not be decrypted automatically when you attempt to use the symmetric key. This is because the “service master key” is used to automatically open the database’s master key. At this point there are a couple of options in order to get encryption working again in the database:

1) Decode all data on source instance; create backup; restore backup; recreate master key, certificate, and encryption key, then re-encrypt the data… yeh, right.

2) Explicitly OPEN MASTER KEY every time you want to use it… this is problematic as it will require code changes.

3) BACKUP SERVICE MASTER KEY on the original instance and use RESTORE SERVICE MASTER KEY on the target server… This can be bad as it hoses up the MASTER KEYS in all the databases on the instance.

4) You can back up the certificates on the original database; create database backup; restore database on target server; drop the keys, certificates, and master key in restored database; recreate the master key; restore the certificate from the backup file; recreate the encryption key. Probably a good practice to backup the certificate, but still is a lot of work.

–TO BACK UP CERTIFICATE ON SOURCE INSTANCE
BACKUP CERTIFICATE SB_CERT
TO FILE = ‘C:SANDBOX_CERT.cer’
WITH PRIVATE KEY ( FILE = ‘C:SANDBOX_CERT_Key.pvk’
, ENCRYPTION BY PASSWORD = ‘SandBox_P@$$w0rd’ )

–ON TARGET INSTANCE AFTER DB RESTORE
–AND COPY CERT FILES TO TARGET’S FILE SYSTEM
CREATE CERTIFICATE SB_CERT
FROM FILE = ‘C:SANDBOX_CERT.cer’
WITH PRIVATE KEY ( FILE = ‘C:SANDBOX_CERT_Key.pvk’
, DECRYPTION BY PASSWORD = ‘SandBox_P@$$w0rd’ )

5) Easiest solution is use the following command on the restored database:

USE dbname
GO

OPEN MASTER KEY DECRIPTION BY PASSWORD = ‘MyEncryptionPassword’
GO

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = ‘MyEncryptionPassword’
GO

NOTE: The master key password needs to match the password you used to create master key on original database. You also need CONTROL permission on database to fiddle with master key.

USE [database]
GO

ALTER TABLE [dbo].[t_user]
DROP COLUMN [password]
GO

ALTER TABLE [dbo].[t_user]
ADD [password] varbinary(20) NULL
GO

UPDATE t_user
SET password = HashBytes(‘SHA1’, cast(‘PASSWORDSTRING’ as nvarchar(16)))
GO

SELECT count(1)
FROM t_user
WHERE password = HashBytes(‘SHA1’, cast(‘PASSWORDSTRING’ as nvarchar(16)))
GO

ALTER PROCEDURE p_UserValidate(@username varchar(75), @password nvarchar(16))
AS
BEGIN
SET NOCOUNT ON;

–Returns userID if user/password combo IS valid
–Returns empty recordset if user/password combo IS NOT valid

SELECT A.userID
FROM t_user A WITH (NOLOCK)
WHERE upper(A.username) = upper(@username)
AND A.password = HashBytes(‘SHA1’, @password)
END
GO

ALTER PROCEDURE p_PasswordChange(@userID numeric(18,0), @password nvarchar(16))
AS
BEGIN
SET NOCOUNT ON;

UPDATE t_user
SET password = HashBytes(‘SHA1’, @password)
WHERE userID = @userID
END
GO

More Info: http://msdn.microsoft.com/en-us/library/ms174415.aspx

A quick way to generate script to disable all FK constraints in current db:

SELECT ‘ALTER TABLE ‘ + B.name + ‘ NOCHECK CONSTRAINT ‘ + C.name
FROM sysconstraints A
, sysobjects B
, sysobjects C
WHERE A.id = B.id
AND A.constid = C.id
AND C.type = ‘F’

A quick way to generate script to enable all FK constraints in current db:

SELECT ‘ALTER TABLE ‘ + B.name + ‘ CHECK CONSTRAINT ‘ + C.name
FROM sysconstraints A
, sysobjects B
, sysobjects C
WHERE A.id = B.id
AND A.constid = C.id
AND C.type = ‘F’

This sql generates a grant blanket execute on stored procedures in a database:

GRANT EXECUTE TO {USER_NAME}
GO

This sql generates a grant execute statement for each stored procedure in the database:

SELECT ‘GRANT EXECUTE ON ‘ + name + ‘ TO {USER_NAME}’
FROM sysobjects
WHERE type IN ( ‘P’, ‘FN’ )
AND name NOT LIKE ‘dt_%’
ORDER BY name

This scripts grants execute to all stored procedures in the database:

USE {DATABASE_NAME}
GO

DECLARE @proc_name varchar(128)
DECLARE @sql nvarchar(255)
DECLARE proc_cur CURSOR FAST_FORWARD
FOR
SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE type IN (‘P’,’FN’)
OPEN proc_cur
FETCH proc_cur INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘grant execute on ‘ + @proc_name + ‘ to {USER_NAME}’
EXECUTE sp_executesql @sql
FETCH proc_cur INTO @proc_name
END
CLOSE proc_cur
DEALLOCATE proc_cur
GO

To create the linked server:

USE [msdb]
GO

EXEC sp_addlinkedserver @server = N’SAMPLE_LINK’, @srvproduct=N’Oracle’, @provider=N’OraOLEDB.Oracle’, @datasrc=N’LINKDSN’
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘SAMPLE_LINK’, @rmtuser = ‘LINKUSER’, @rmtpassword = ‘*******’, @useself = False
GO

To drop the linked server:

USE [msdb]
GO

EXEC sp_droplinkedsrvlogin ‘SAMPLE_LINK’, NULL
GO

EXEC sp_dropserver ‘SAMPLE_LINK’
GO

CREATE FUNCTION fn_Split

( @text varchar(8000)

, @delimiter varchar(20) = ' '

)
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY
, value varchar(8000)   
)
AS
BEGIN

DECLARE @index int 

SET @index = -1 

WHILE (LEN(@text) > 0) 
 BEGIN  
   SET @index = CHARINDEX(@delimiter , @text)  
   IF (@index = 0) AND (LEN(@text) > 0)  
    BEGIN   
     INSERT INTO @Strings VALUES (@text)
         BREAK  
    END  
   IF (@index > 1)  
    BEGIN   
      INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
      SET @text = RIGHT(@text, (LEN(@text) - @index))  
    END  
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index)) 
 END
RETURN
END
GO

USE [dbname]
GO

—————————————-
— Drop all constraints (PKs & FKs)
—————————————-
SET NOCOUNT ON
DECLARE @id int
, @objname nvarchar(256)
, @constr_name nvarchar(128)
, @count int
, @sql nvarchar(2000)
DECLARE @objs AS TABLE ( id int identity(1,1), objname nvarchar(256), objtype varchar(128))
INSERT INTO @objs ( objname, objtype )
SELECT table_schema + ‘.’ + table_name
, constraint_name
FROM information_schema.table_constraints
ORDER BY table_name
, constraint_type
SELECT @id = max(id) FROM @objs
SET @count = @id
WHILE @id > 0
BEGIN
SELECT @objname = objname
, @constr_name = objtype
FROM @objs WHERE id = @id
SET @sql = ‘ALTER TABLE ‘ + @objname + ‘ DROP CONSTRAINT [‘ + @constr_name + ‘]’
PRINT @sql
–EXECUTE sp_executesql @sql
SET @id = @id – 1
SET @count = @count + 1
END
PRINT CAST(@count as varchar(10)) + ‘ Constraint(s) deleted’
GO

—————————————-
— Drop all Indexes
—————————————-
SET NOCOUNT ON
DECLARE @id int
, @tabname nvarchar(128)
, @idxname nvarchar(128)
, @ispk tinyint
, @count int
, @sql nvarchar(2000)
DECLARE @objs AS TABLE ( id int identity(1,1), tabname nvarchar(128), idxname varchar(128), ispk tinyint )
INSERT INTO @objs ( tabname, idxname, ispk )
SELECT OBJECT_NAME(si.object_id) “tabname”
, si.name “idxname”
, si.Is_Primary_Key “ispk”
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc
ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, ‘IsUserTable’) = 1
AND si.name IS NOT NULL
ORDER BY 1, 2
SELECT @id = max(id) FROM @objs
WHILE @id > 0
BEGIN
SELECT @tabname = tabname
, @idxname = idxname
, @ispk = ispk
FROM @objs
WHERE id = @id
SET @sql = CASE @ispk when 1 then ‘ALTER TABLE [‘ + @tabname + ‘] DROP CONSTRAINT [‘ + @idxname + ‘]’
else ‘DROP INDEX [‘ + @idxname + ‘] ON [‘ + @tabname + ‘] WITH ( ONLINE = OFF )’ END
PRINT @sql
SET @id = @id – 1
END
GO

use sandbox1
go

— Script out indexes completely, including both PK’s and regular indexes, each clustered or nonclustered.
— DOES NOT HANDLE COMPRESSION; that’s ok, since 2008 R2 RTM benchmarking shows it’s faster and results in smaller indexes to insert uncompressed and then compress later
— HARDCODES [dbo] schema (i.e. it doesn’t say [JohnDoe].[table], changing that to [dbo].[table]
— modified from original version from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(MAX),
@sIndexCols VARCHAR(MAX),
@sSQL VARCHAR(MAX),
@sParamSQL VARCHAR(MAX),
@sFilterSQL VARCHAR(MAX),
@location SYSNAME,
@IndexCount INT,
@CurrentIndex INT,
@CurrentCol INT,
@Name VARCHAR(128),
@IsPrimaryKey TINYINT,
@Fillfactor INT,
@FilterDefinition NVARCHAR(MAX),
@IsClustered BIT, — used solely for putting information into the result table
@PKDropSQL NVARCHAR(MAX)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]’))
DROP TABLE [dbo].[#IndexSQL]

CREATE TABLE #IndexSQL
( TableName VARCHAR(128) NOT NULL
,IndexName VARCHAR(128) NOT NULL
,IsClustered BIT NOT NULL
,IsPrimaryKey BIT NOT NULL
,IndexCreateSQL NVARCHAR(max) NOT NULL
,PKDropSQL NVARCHAR(max) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]’))
DROP TABLE [dbo].[#IndexListing]

CREATE TABLE #IndexListing
(
[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID] INT NOT NULL,
[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID] INT NOT NULL,
[IsPrimaryKey] TINYINT NOT NULL,
[FillFactor] INT,
[FilterDefinition] NVARCHAR(MAX) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]’))
DROP TABLE [dbo].[#ColumnListing]

CREATE TABLE #ColumnListing
(
[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable] INT NOT NULL,
[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex] INT NOT NULL,
[IsIncludedColumn] BIT NULL
)

INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL –si.filter_definition –filter_definition is in sql 2008
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, ‘IsUserTable’) = 1
ORDER BY OBJECT_NAME(si.object_id), si.index_id

SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

WHILE @CurrentIndex 0
BEGIN

SELECT @CurrentCol = 1

SELECT @IxFirstColumn = 1, @sIncludeCols = ”, @sIndexCols = ”

WHILE @CurrentCol 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ‘,’
END

SET @sIncludeCols = @sIncludeCols + ‘[‘ + @IxColumn + ‘]’

END

SET @CurrentCol = @CurrentCol + 1
END

TRUNCATE TABLE #ColumnListing

–append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ‘) ‘ + ‘ INCLUDE ( ‘ + @sIncludeCols + ‘ ) ‘
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ‘) ‘

— Add filtering
IF @FilterDefinition IS NOT NULL
SET @sFilterSQL = ‘ WHERE ‘ + @FilterDefinition + ‘ ‘ + CHAR(13)
ELSE
SET @sFilterSQL = ”

— Build the options
SET @sParamSQL = ‘WITH ( PAD_INDEX = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsPadIndex’) = 1
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ ALLOW_PAGE_LOCKS = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsPageLockDisallowed’) = 0
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ ALLOW_ROW_LOCKS = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsRowLockDisallowed’) = 0
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ STATISTICS_NORECOMPUTE = ‘

— THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
IF (INDEXPROPERTY(@idxTableID, @idxname, ‘IsStatistics’) = 1)
SET @sParamSQL = @sParamSQL + ‘ON’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF’

— Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
IF ISNULL( @FillFactor, 90 ) 0
SET @sParamSQL = @sParamSQL + ‘ ,FILLFACTOR = ‘ + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )

IF (@IsPrimaryKey = 1) — DROP_EXISTING isn’t valid for PK’s
BEGIN
SET @sParamSQL = @sParamSQL + ‘ ) ‘
SET @PKDropSQL = ‘ALTER TABLE [‘ + @idxTableName + ‘] DROP CONSTRAINT [‘ + @idxName + ‘]’
END
ELSE
BEGIN
SET @sParamSQL = @sParamSQL + ‘ ,DROP_EXISTING = ON ) ‘
SET @PKDropSQL = NULL
END

SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL

— 2008 R2 allows ON [filegroup] for primary keys as well, negating the old “IF THE INDEX IS NOT A PRIMARY KEY – ADD THIS – ELSE DO NOT” IsPrimaryKey IF statement
SET @sSQL = @sSQL + ‘ ON [‘ + @location + ‘]’

–PRINT @sIndexCols + CHAR(13)
INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, PKDropSQL)
VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @PKDropSQL)

END

SET @CurrentIndex = @CurrentIndex + 1
END

SELECT * FROM #IndexSQL