Data encyption using symmetric keys outside SQL Server

Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.

I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.

Please advise me if my approach is correct and what steps I need to take.

Importing or exporting key material for SYMMETRIC KEYs is not supported in SQL Server 2005. SYMMETRIC KEY material is always encrypted in the database and we don’t have any access point where we display such material in an unprotected form for security reasons, because of this SYMMETRIC KEYS as well as ciphertext created by EncryptByKey are only meant to be consumed by SQL Server.

-Raul Garcia

SDE/T

SQL Server Engine

|||Thank you for the response. I suspected as much for the very reasons you mentioned.
I did some work on asymetric keys but wasn't successful. Can you tell me the correct strategy to expose the public key so I can use it to encrypt within the SSIS package.|||

Here is a link that should be useful. In this link the author was also using ASYMMETRIC KEYS in SQL Server and VB .Net:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information will be useful,but let us know if there is anything else we can do to help.

-Raul Garcia

SDE/T

SQL Server Engine

Data encryption and keys

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

As long as the user you are trying to protect against is not a dbo or sysadmin, you can also use permissions (i.e. "GRANT CONTROL ON ASYMMETRIC KEY :: MyAsymmKey TO user1") to restrict access rather than through passwords. The advantage is the user then doesn't have to depend on memorizing a password and you don't have to pass any password values in which is safer from a security standpoint.

Sung

|||Fyi, Books online links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.

Data Encryption

Is there anyone out there practicing data encryption in
their database ? Column data or table data (Not Stored
P.). If so, how can I apply it to my database too ?
Management wants even the data in the tables encrypted to
the DBA.
If there are any third party utilities out there, can
someone direct me to them ?.
T.I.A
You can find some third party tools listed in the encryption
section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Fri, 11 Feb 2005 08:34:10 -0800, "Chris"
<anonymous@.discussions.microsoft.com> wrote:

>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them ?.
>T.I.A
|||In message <210201c51057$83d37640$a601280a@.phx.gbl>, Chris
<anonymous@.discussions.microsoft.com> writes
>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them ?.
>T.I.A
Personally, I have embedded AES within one of our Utility DLL's and when
I need to protect columns (ie: passwords or pin numbers - anything
really) it is called prior to updating SQL Server (ie: via INSERT or
UPDATE).
Secondly, it is very rare that this data needs decrypting (or else
what's the point) so only ever compare encrypted values in SELECT's.
There are some third party utilities that can help with this issue (try
Google) but from experience I would tell Management that you could
provide encryption to individual columns but its not practical to
implement this for all data (after all there is a big overhead,
specially on large databases).
The best protection for the database from users is for the DBA to set it
up right in the first place by using SQL Logins to restrict access to
only the tables, views and stored procedures they require. This then
means of course that the applications accessing the data use the right
credentials for the each operation, feature and user (ie: by design).
As for protecting the data from DBA's I would suggest this is done by
employing the right people in the first place; adding various clauses to
employment contracts; signing NDA's; and of course threatening with very
serious law suits and of course don't forget the base ball bats <g>.
The ultimate protection for the database from everyone (specially those
in Management) would be to turn the Computer OFF; Lock it in a Nuclear
bunker; destroy the key and then burn the maps to its location. <g>
Alternatively, don't store the data in the first place. <bg>
I think Yukon might have some new features in this area as well, however
someone else will need to answer that one. Besides, Yukon is not hear
yet !
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Data Encryption

Is there anyone out there practicing data encryption in
their database ? Column data or table data (Not Stored
P.). If so, how can I apply it to my database too ?
Management wants even the data in the tables encrypted to
the DBA.
If there are any third party utilities out there, can
someone direct me to them '.
T.I.AYou can find some third party tools listed in the encryption
section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Fri, 11 Feb 2005 08:34:10 -0800, "Chris"
<anonymous@.discussions.microsoft.com> wrote:
>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them '.
>T.I.A|||In message <210201c51057$83d37640$a601280a@.phx.gbl>, Chris
<anonymous@.discussions.microsoft.com> writes
>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them '.
>T.I.A
Personally, I have embedded AES within one of our Utility DLL's and when
I need to protect columns (ie: passwords or pin numbers - anything
really) it is called prior to updating SQL Server (ie: via INSERT or
UPDATE).
Secondly, it is very rare that this data needs decrypting (or else
what's the point) so only ever compare encrypted values in SELECT's.
There are some third party utilities that can help with this issue (try
Google) but from experience I would tell Management that you could
provide encryption to individual columns but its not practical to
implement this for all data (after all there is a big overhead,
specially on large databases).
The best protection for the database from users is for the DBA to set it
up right in the first place by using SQL Logins to restrict access to
only the tables, views and stored procedures they require. This then
means of course that the applications accessing the data use the right
credentials for the each operation, feature and user (ie: by design).
As for protecting the data from DBA's I would suggest this is done by
employing the right people in the first place; adding various clauses to
employment contracts; signing NDA's; and of course threatening with very
serious law suits and of course don't forget the base ball bats <g>.
The ultimate protection for the database from everyone (specially those
in Management) would be to turn the Computer OFF; Lock it in a Nuclear
bunker; destroy the key and then burn the maps to its location. <g>
Alternatively, don't store the data in the first place. <bg>
I think Yukon might have some new features in this area as well, however
someone else will need to answer that one. Besides, Yukon is not hear
yet !
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Data Encryption

Is there anyone out there practicing data encryption in
their database ? Column data or table data (Not Stored
P.). If so, how can I apply it to my database too ?
Management wants even the data in the tables encrypted to
the DBA.
If there are any third party utilities out there, can
someone direct me to them '.
T.I.AYou can find some third party tools listed in the encryption
section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Fri, 11 Feb 2005 08:34:10 -0800, "Chris"
<anonymous@.discussions.microsoft.com> wrote:

>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them '.
>T.I.A|||In message <210201c51057$83d37640$a601280a@.phx.gbl>, Chris
<anonymous@.discussions.microsoft.com> writes
>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them '.
>T.I.A
Personally, I have embedded AES within one of our Utility DLL's and when
I need to protect columns (ie: passwords or pin numbers - anything
really) it is called prior to updating SQL Server (ie: via INSERT or
UPDATE).
Secondly, it is very rare that this data needs decrypting (or else
what's the point) so only ever compare encrypted values in SELECT's.
There are some third party utilities that can help with this issue (try
Google) but from experience I would tell Management that you could
provide encryption to individual columns but its not practical to
implement this for all data (after all there is a big overhead,
specially on large databases).
The best protection for the database from users is for the DBA to set it
up right in the first place by using SQL Logins to restrict access to
only the tables, views and stored procedures they require. This then
means of course that the applications accessing the data use the right
credentials for the each operation, feature and user (ie: by design).
As for protecting the data from DBA's I would suggest this is done by
employing the right people in the first place; adding various clauses to
employment contracts; signing NDA's; and of course threatening with very
serious law suits and of course don't forget the base ball bats <g>.
The ultimate protection for the database from everyone (specially those
in Management) would be to turn the Computer OFF; Lock it in a Nuclear
bunker; destroy the key and then burn the maps to its location. <g>
Alternatively, don't store the data in the first place. <bg>
I think Yukon might have some new features in this area as well, however
someone else will need to answer that one. Besides, Yukon is not hear
yet !
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Data coversion or Derived column?

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

milton06 wrote:

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

You'll need a derived column component for that. Here's the expression

RIGHT("000" + (DT_STR, 3, 1252)[columnname], 3)

-Jamie

|||Excellent Jamie.|||

milton06 wrote:

Excellent Jamie.

Please mark his post as the answer if that solved your problem.

Thanks,
Phil

Data Conversion failed due to Potential Loss of data

Hi,

I am getting this error when my ssis package is running

Data Conversion failed due to Potential Loss of data

the input column is in string format and output is in sql server bigint

the error is occuring when there is an empty string in the input. what should i do to overcome this

It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.

Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

|||

desibull wrote:

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

NULL and "empty string" are two very different things.

To expand on what I suggested earlier and desibull's code above:

ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]

OR

ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]

Data Conversion and Derived Column issue

Hi all of you,

I think that I've done a big mess on my work... I've got plain file which must be loaded into a sql table. Up to there no problem, I use Derived Column due to columns needed be transformed with NULL, RIGHT, LEN, and so on...

But in this last package I've done half of work using Data Conversion but I've got five columns which would need be transformed but I don't know how can I do such thing. I can't connect a Derived Column from Flat File Source task, of course, it's already data conversion...

Let me know if you need further details.

Previously I think, silly idea, that it could be unified or better, that Data Conversion task allows me make transformations...

Thanks a lot for your ideas and thoughs,

Can't you just connect the output from the Data Conversion transform to the Derived Column transform, then connect it to your destination component?

|||

Yes, you're totally right. Everything's fine.

thanks

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed th
e
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly adviseTiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed the
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly advise
Tiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed the
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly adviseTiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty existing table!

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.

ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO

You should change it to these statements in SQLServer2000:

ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO

ah, ridiculous! right?

Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.Hi,

You can use a workaround in this case... Put a DEFAULT constraint on
your column and it will work...

Enjoy,

Cdric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
aling a crit :

Quote:

Originally Posted by

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.
>
ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO
>
You should change it to these statements in SQLServer2000:
>
ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO
>
ah, ridiculous! right?
>
Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.

Daft DTS Data Driven task question

Hi, I'm trying to get a Data Driven DTS task to update a table based on a .csv source file. I've got a numeric ID Identity column (unique) to give me a record number, and I'm using the following code on the update query to try and update the relevant record:

UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)

The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.

Anyone know the answer - I'll bet it's simple :)

Cheers,

MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.

Very strange.

CVS Export in ASCII format

For the CSV export problem, where excel opens data into one column, I
had to add a report link using the below code to fix the problem. Is
there a way to change the underlying export encoding in reporting
services?
="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"There is for RS 2005. Not for RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<slov1@.hotmail.com> wrote in message
news:1135297330.158158.120010@.g49g2000cwa.googlegroups.com...
> For the CSV export problem, where excel opens data into one column, I
> had to add a report link using the below code to fix the problem. Is
> there a way to change the underlying export encoding in reporting
> services?
> ="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"
>