Data Encryption In SQL2005

I am a developer and need to store some confidential information in my DB. I
came to know about SQL server 2005's encryption features recently.
I want to encrypt data in such a way that even DBA's who created the DB's
(owners) should not be able to decrpt the data.
Is it possibel in SQL server 2005 ? if so, can anyone breifly outline the
steps or point to ncessary location.
thanks. KumarHi
Have a look at Laurentiu Cristofor's blog
http://blogs.msdn.com/lcris/default.aspx
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:89F62EDD-1D58-4781-96CE-5FC5E6E82C8F@.microsoft.com...
>I am a developer and need to store some confidential information in my DB.
>I
> came to know about SQL server 2005's encryption features recently.
> I want to encrypt data in such a way that even DBA's who created the DB's
> (owners) should not be able to decrpt the data.
> Is it possibel in SQL server 2005 ? if so, can anyone breifly outline the
> steps or point to ncessary location.
> thanks. Kumar
>|||Mike,
Thanks. Will try it. Do I need to have any special privileges to create all
these keys ?
thanks Kumar
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Have a look at Laurentiu Cristofor's blog
> http://blogs.msdn.com/lcris/default.aspx
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Kumar" <Kumar@.discussions.microsoft.com> wrote in message
> news:89F62EDD-1D58-4781-96CE-5FC5E6E82C8F@.microsoft.com...
>
>|||Yes, you need to have been granted special permissions for creating keys.
For details, look in Books Online; the permission requirements for DDL are
listed there.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:4DDBF38B-32FA-4284-A273-225F1D0B79A0@.microsoft.com...[vbcol=seagreen]
> Mike,
> Thanks. Will try it. Do I need to have any special privileges to create
> all
> these keys ?
> thanks Kumar
> "Mike Epprecht (SQL MVP)" wrote:
>|||Guys, thank you very much.
"Laurentiu Cristofor [MSFT]" wrote:

> Yes, you need to have been granted special permissions for creating keys.
> For details, look in Books Online; the permission requirements for DDL are
> listed there.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Kumar" <Kumar@.discussions.microsoft.com> wrote in message
> news:4DDBF38B-32FA-4284-A273-225F1D0B79A0@.microsoft.com...
>
>sql

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is written
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is know
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive...encryption.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is writt
en
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is kn
ow
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

Data display in table

Hi,

I have a result set from a store procedure which looks like this.

Column1 Column2 Column3

A 1 lit1

A 1 Rep2

A 1 Reg3

B 2 ram1

B 2 lim2

I want to show data in my table like this:

Column1 Column2 Column3

A 1 lit1; Rep2;Reg3

B 2 ram1;lim2

Can anyone tell me how I can do this?

Thanks

Ashwini

Hi,

While getting the result from the database you can do one thing i.e write a Scalar valued function which will take the Column 1 value in that stored procedure write the logic to get the Column3 values separated by semi colon like this

ALTER FUNCTION [dbo].[GetColumn3]

(

@.Column1 bigint //here datatype of your column

)

RETURNS varchar(max)

AS

BEGIN

DECLARE @.Result varchar(max)

SET @.Result = ''

--Select all Column3 into a string seperated by ';' for a column1

SELECT @.Result = @.Result + CONVERT(VARCHAR(MAX), p.Column3) + ', '

from //your table table1

WHERE

AND table1.(Your Column name here) = @.Column1

//This code to remove your last semi colon

IF @.Result != ''

SELECT @.Result= SUBSTRING(@.Result , 1, LEN(@.Result )-1)

RETURN @.Result

END

and call that function in your main Select query:

Select Column1,Column2 , dbo.GetColumn3(Column1) as Column3

From table1

joins

Where Conditions

Hope this helps.

sql

Hi,
Is there anything analogous to a "data dicitionary" tool that is bundled with SQL Server 2005? Something that could be used to store and manage enterprise data meta-data.
Thanks,
JR

The system tables and views...following:

Data dictionary

From Wikipedia, the free encyclopedia

A data dictionary is a set of metadata that contains definitions and representations of data elements. Within the context of a DBMS, a data dictionary is a read-only set of tables and views. The data dictionary is database in its own dictionary. Amongst other things, a data dictionary holds the following information:

Precise definition of data elements

Usernames, roles and privileges

Schema objects

Integrity constraints

Stored procedures and triggers

General database structure

Space allocations|||Hi,
Thanks for the info. May I assume that these system tables and their contents are officially documented somewhere? Any pointers to any such docs would be greatly appreciated.
Regards,
JR|||

In Object Explorer in SQL Server Management Studio, look under Databases/System Databases/master/Views. Master contains server level information. Similarly Databases/System Databases/msdb/Views | Tables has server level information for SQL Agent, Database Mail, Log Shipping and other technologies.

Each user database also has system views.

With the exception of a few internal views and tables each view or table has a topic in Books Online.

|||True most of the information pertaining to the components & objects of SQL Server are documented in Books online, there are few bits and pieces that are not documented in BOL where you can find information by searching them on web.

Hi,
Is there anything analogous to a "data dicitionary" tool that is bundled with SQL Server 2005? Something that could be used to store and manage enterprise data meta-data.
Thanks,
JR

The system tables and views...following:

Data dictionary

From Wikipedia, the free encyclopedia

A data dictionary is a set of metadata that contains definitions and representations of data elements. Within the context of a DBMS, a data dictionary is a read-only set of tables and views. The data dictionary is database in its own dictionary. Amongst other things, a data dictionary holds the following information:

Precise definition of data elements Usernames, roles and privileges Schema objects Integrity constraints Stored procedures and triggers General database structure Space allocations|||Hi,
Thanks for the info. May I assume that these system tables and their contents are officially documented somewhere? Any pointers to any such docs would be greatly appreciated.
Regards,
JR
|||

In Object Explorer in SQL Server Management Studio, look under Databases/System Databases/master/Views. Master contains server level information. Similarly Databases/System Databases/msdb/Views | Tables has server level information for SQL Agent, Database Mail, Log Shipping and other technologies.

Each user database also has system views.

With the exception of a few internal views and tables each view or table has a topic in Books Online.

|||True most of the information pertaining to the components & objects of SQL Server are documented in Books online, there are few bits and pieces that are not documented in BOL where you can find information by searching them on web.

Data access From sqlserver 2000

Hello

i have one prob . i m using sql server 2000 . and i have write a store procedure to fetch data from multiple table using cursor . this query exculate in sql server . but i can't fetch this data from in our page . how to fetch data from using multiple table .plz help

thnx

Hi shruram.gore,

You don't need to use cursor for this unless you need to do some operations (processess) before displaying the data.
Cursor will not help you in displying the data, to display the data use SELECT and if you need to fetch the data from more than one table, use JOIN.

Example:

SELECT a.col1, a.col2, b.col2FROM table1 aINNERJOIN table2 bon (a.id = b.id)

Good luck.

|||

thnx , for suggestion

But dont have fix table name . in my prob my table name is variable plz check the query . this query we will use to bind treeView to show parent child releation . i have execute data from this query but we cant fetch data in dataset

thanx in advance

CREATE PROCEDURE [dbo].GetRootChild
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @.parentId int
declare @.TableName varchar (50)
declare @.RelIDName char(10),@.s int,@.sql varchar(4000)

declare comments_list CURSOR FOR
select distinct [PcRelation].[ChID],[ObjectType].[ObjName],[ObjectType].[RelIdName] from PcRelation,ObjectType where [PcRelation].[ChObjectID]=[ObjectType].[ID] and [PcRelation].[PID]='0' and [PcRelation].[PObjectID]='0'
OPEN comments_list
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE product_cursor CURSOR FOR
select @.TableName as TableName
--select * from Company
select @.sql ='select * from [dbo].['+ @.TableName +'] where ['+ @.TableName +'].['+@.RelIDName+']= '+convert(char(10),@.parentId)+''
OPEN product_cursor
FETCH NEXT FROM product_cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
--select @.TableName as TableName
exec(@.sql)
FETCH NEXT FROM product_cursor
END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
END
CLOSE comments_list
DEALLOCATE comments_list
GO

and fetch data from sql

if cant image refer this url

http://shriramgore.ifastnet.com/?q=node/41

thnx

|||

Hi,

Based on my understing, since you didn't do any row-based operations on the table(after you have fetched each row, seems you didn't perform any operations), there is no need for you to use cursors. If what you want is just to fetch data from multi tables, a simpleJoin will work in your case. Use dynamic query if you cannot determine your table name until run-time. Like:

declare @.str nvarchar(100) ,@.str1 varchar(100)set @.str='table_1'set @.str1='table_2'exec ('select * from '+@.str+' as a inner join '+ @.str1+' as b on a.id=b.id')

Hope my suggestion helps|||

thnx for replyBo Chen

but i have following like ......... please check example ...

I have data from multiple table, those table's Names are store as Variable in a table . in this Case i have written 1 store procedure using Cursor . this query Execute in server like (Please check this Url -http://shriramgore.ifastnet.com/?q=node/41&size=_original ) . after executing storeprocedure i get data in different tabels this data i can't fetch in single dataset .if i will fetch all tables(results of storeprocedure) then it eaiser to me for next part .

e.g
DataSet ds = new DataSet();
ds = proc.GetDataSet();

for (int i= 0; i< ds.Tables.Count; i++)
{
for (int y= 0; y< ds.Tables [i].Rows.Count; y++)
{
String str=ds.tabels[i].rows[y]["ColumnName"].toString();
}
}

in this example i enable to get other tabels (dsParent1.Tables[int] ).

please help

thnx in advance

|||

Hi shriram.gore,

I can see your problem. In my opinion, i think you can try to revise your stored procedure, like this: pass three or four parameters(as you need) into your stored procedure and make them as output paramters. In your stored procedure, you can assign them the table-name values during sql clause execution. For example:

create procedure sp_get_tbl_name (@.str1 varchar(100) output,
@.str2 varchar(100) output, @.str3 varchar(100) output...)
as
select @.str1=tablename
from yourtablename
where .....
go
select @.str2=tablename
from yoursecondtablename
where...
go
..

Hope my suggestion helps

|||

Hello ,

thnx for Reply

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

excuse i m not using sqlserver 2005

thnx

shriram

|||

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

If i understand you correct, that's not a problem.

Since now you have got those table names, you can user your sldataadpater to perform a sql data retrieval against each data table name--after that you will get server datasets, say, ds1, ds2,ds3,ds4..etc. Generally speaking each dataset you get contains only one table(it depends on your select command)

Then you can create a new dataset named ds, run : ds.tables.add(ds1.table[0]); ds.tables.add(ds2.table[0]),ds.tables.add(ds3.table[0])...etc..

Now your dataset ds contains all the tables you need. You can perform your loop on it.

BTW, what does "m i rt. " stand for??

thanks

|||

HelloBo Chen ,

thank u very much . my prob is solved .

thnx

shriram

Data about Stored Procudre

How i get data about stored procedures

Data like stored procudres name

Name of parameters in store procudre .

And type of parameters in stored procedure.

Check this

select a.Name,b.Name as ParameterName,c.name as Datatype,b.Length From
(select *from sysobjects where xtype='p') a
inner join
Syscolumns b on a.id=b.id

inner join

Systypes c ON c.xtype=b.xtype

Madhu

|||

I found way to take detail

select * from INFORMATION_SCHEMA.PARAMATERS

data

I am making a web site of a magazin and when i try to store data in the tabl
e
,the data was larger . and i asked how can i store the large text in the
table and if it possible to store text file?how can i store the large text
--See my previous post on your thread for storing text.
As long as the text is less than 8000 bytes you can use varchar
(nvarchar(4000)). For a size above this limit
you can use TEXT (2^31 - 1 (2.147.483.647) bytes or 2^30 - 1 (1.073.741.823)
bytes -->NTEXT.
-store text file
You are able to store binary data, such as files:
"Generally speaking, you use text to store huge ASCII character strings,
ntext for Unicode character strings, and image for binary image data. "
It can store up to (2^31 - 1 (2.147.483.647) bytes .
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Zoza" <Zoza@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DD62FB13-CB0A-4A8C-A946-84D1ACA5A8F7@.microsoft.com...
>I am making a web site of a magazin and when i try to store data in the
>table
> ,the data was larger . and i asked how can i store the large text in the
> table and if it possible to store text file?|||Thanks
I really try to use txt and ntxt to specify the type of data ,but when i try
to store data it was very large.
I want to make a table of news that contain title of news and the details of
news ,no problem of storing title,but the details of news was no longer.
I can't know what i do?
"Jens Sü?meyer" wrote:

> how can i store the large text
> --See my previous post on your thread for storing text.
> As long as the text is less than 8000 bytes you can use varchar
> (nvarchar(4000)). For a size above this limit
> you can use TEXT (2^31 - 1 (2.147.483.647) bytes or 2^30 - 1 (1.073.741.82
3)
> bytes -->NTEXT.
> -store text file
> You are able to store binary data, such as files:
> "Generally speaking, you use text to store huge ASCII character strings,
> ntext for Unicode character strings, and image for binary image data. "
> It can store up to (2^31 - 1 (2.147.483.647) bytes .
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Zoza" <Zoza@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:DD62FB13-CB0A-4A8C-A946-84D1ACA5A8F7@.microsoft.com...
>
>|||Was the data tool arge for the ntext / text column. I wonder that if so any
human would ever read this article containing more than 2^31 bytes ? Or what
did you want to say with "it was very large".
And what does "details of news was no longer" mean ?
Jens Suessmeyer.
"Zoza" <Zoza@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F8DA783D-0A94-4347-AE48-72E96E8628CC@.microsoft.com...
> Thanks
> I really try to use txt and ntxt to specify the type of data ,but when i
> try
> to store data it was very large.
> I want to make a table of news that contain title of news and the details
> of
> news ,no problem of storing title,but the details of news was no longer.
> I can't know what i do?
> "Jens Smeyer" wrote:
>