SQL Server encryption and decryption data with keys and certificate

create database mydb

-----------------------------------------------------------------------------

CREATE TABLE Employee

(Emp_ID int NOT NULL,

Emp_Date datetime DEFAULT GETDATE() NOT NULL,

Emp_Comments varchar(2000) NOT NULL)

-----------------------------------------------------------------------------

USE [master]

GO

CREATE LOGIN [User1] WITH PASSWORD=N'Pa$$w0rd'

GO

USE [mydb]

GO

CREATE USER [User1] FOR LOGIN [User1]

GO

-----------------------------------------------------------------------------

GRANT SELECT, INSERT, UPDATE ON Employee

TO User1

-- Create database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- Create certificate

CREATE CERTIFICATE MyCert

AUTHORIZATION User1

WITH SUBJECT = 'test certificate'

GO

-- Create symmetric key

CREATE SYMMETRIC KEY MyKey

AUTHORIZATION User1

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE myCert

GO

-- Insert encrypted data

EXECUTE AS USER = 'User1'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert

INSERT INTO Employee

VALUES

(1, DEFAULT, EncryptByKey(Key_GUID('MyKey'),'Employee comment data'))

CLOSE ALL SYMMETRIC KEYS

REVERT

-- Read the decrypted data

EXECUTE AS USER = 'User1'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert

SELECT CONVERT(varchar,DecryptByKey(Emp_Comments)) AS Comments FROM Employee

CLOSE ALL SYMMETRIC KEYS

REVERT

Inserting files to database

inserting data from files (jpg, xml, avi ....) into database, easy by the follwing code.

INSERT INTO Photos (FileName, FileType, Picture)

SELECT 'Photo1.jpg' AS FileName,
'.jpg' AS FileType,
* FROM OPENROWSET(BULK N'C:\Photo1.jpg', SINGLE_BLOB) AS Picture

as you see, in the section of bulk you put the path od the file that you want to put it in your database. any kind of file you can put. but you need to specify your data type column to varbinary(max) .

the following code try to insert multiple pics into database.

Declare @imgNumber int

DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)
SET @imgNumber = 1
WHILE @imgNumber > 101
BEGIN
SET @imgString = 'E:\images\Picture' + CONVERT(varchar,@imgNumber) + '.jpg'
SET @insertString = N'INSERT INTO images(imageData)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
EXEC(@insertString)
SET @imgNumber = @imgNumber + 1
END
GO

UNIQUEIDENTIFIER with ROWGUIDCOL

Example 1 :

CREATE TABLE MyTable (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), nam varchar(max))


insert into MyTable (nam) values ('mohamed')
insert into MyTable (nam) values ('tareq')
insert into MyTable (nam) values ('emad')
insert into MyTable (nam) values ('ahmed')
select * from MyTable

Example 2 :

CREATE TABLE MyTable2 (ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID() , nam varchar(max))


insert into MyTable2 (nam) values ('mohamed')
insert into MyTable2 (nam) values ('tareq')
insert into MyTable2 (nam) values ('emad')
insert into MyTable2 (nam) values ('ahmed')
select * from MyTable2
select ROWGUIDCOL from MyTable2
select OBJECTPROPERTY (object_id('table1'),'TableHasRowGuidCol')

--Notes


--1) The UNIQUEIDENTIFIER along with ROWGUIDCOL NEWSEQUENTIALID() is far more efficient than normal UNIQUEIDENTIFIER along with NEWID().

--2) The Unique Ids generated with the second approach are sequential in nature, similar to IDENTITY values.

--3) There can be max of one ROWGUIDCOL for a table.

--4) You can query the table for ROWGUIDCOL. Example: SELECT ROWGUIDCOL FROM MyTable

SQL Server 2008 Streaming

it's new feature in sql server 2008. in the installation steps you can support you server to working with stream .

before working with database stream, goto configuration manager and select properties of your instance that you want to act with streaming. 



 then select filestream tab, and click on you need from this tab .
  

then open new query in sql server and write the following code to full access enabed file stream on server level .

-- filestream configuration

-- The last parameter to sp_configure specifies the Access Level,
-- where 0 means ‘Disabled’,
-- 1 means ‘Transact-SQL Access Enabled’ and
-- 2 means ‘Full Access Enabled’
EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

Now, we can create database with streaming option by following code.

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = 'C:\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = 'C:\NorthPoleFS')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\NorthPoleLOG.ldf')
GO



return to database to see the effective of above code .

Return to your query again, and past the following code.

CREATE TABLE [dbo].[Items](

[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

the above code is about create table Items, note the column ItemImage it's varbinary(max) datatype and also filestream. filestrem keyword just attribute behinde varbinary datatype , it's note a datdatype. filestrem keyword found here for working with streaming database.
ItemID column in previous code with datatype UNIQUEIDENTIFIER, for working with streaming in your datdabase add attribute ROWGUIDCOL. this attribute keep UNIQUEIDENTIFIER with sequential result as you know with normal identity column result.
the final step now is to insert binary data to the previous table .
-- Declare a variable to store the image data

DECLARE @img AS VARBINARY(MAX)
-- Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\img1.jpg',
SINGLE_BLOB ) AS x
-- Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Microsoft Mouse', @img

the previous code try to insert image file named 'img1.jpg' to table items.
 
but what about streaming, the above code can be run withtout any previous configuration . it's mean that the streaming database not mean inserting data to database, but it's mean how to read data binary from database by synchronise manner.
 
all previous configuration let you working with streaming database, to read your data by stream manner you can do that by programming language such as vb.net or c#.