Default date in a derived column expression

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

|||Fresh from the Integration Services Expression Reference section of Books Online, we have the GETDATE (http://msdn2.microsoft.com/en-US/library/ms139875.aspx) topic. So using GETDATE is possible, but sounds like a variable or literal casted as you suggest is actually what is needed here.|||

Larry Charlton wrote:

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

That's done it. Thank You.

default date

in DateTime column called ExpireDate, I have default value of (1/1/2020)
yet when data entry is made (without ExpireDate value) the value is always
set at 1/1/1900
why is default not entered as 1/1/2020 ?
DEFAULTs only work when you don't provide a value for the column at all, or
use the keyword DEFAULT. What it looks like is that you provide the value 0
for the column, and 0 as a datetime is interpreted by SQL Server as
1/1/1900. See the following example:
CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
INSERT INTO TJS (Expire_Date) VALUES (0)
INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
SELECT Expire_Date FROM TJS
Jacco Schalkwijk
SQL Server MVP
"TJS" <nospam@.here.com> wrote in message
news:1132gu53p4kt9bd@.corp.supernews.com...
> in DateTime column called ExpireDate, I have default value of (1/1/2020)
> yet when data entry is made (without ExpireDate value) the value is always
> set at 1/1/1900
> why is default not entered as 1/1/2020 ?
>
>
|||your example works, but I am trying to use a stored procedure
I have this in the stored procedure:
@.ExpireDate datetime = DEFAULT
The column default value is set as (1/1/2020)
but it still enters 1/1/1900
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uIdTaWhJFHA.576@.TK2MSFTNGP15.phx.gbl...
> DEFAULTs only work when you don't provide a value for the column at all,
> or use the keyword DEFAULT. What it looks like is that you provide the
> value 0 for the column, and 0 as a datetime is interpreted by SQL Server
> as 1/1/1900. See the following example:
> CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
> INSERT INTO TJS (Expire_Date) VALUES (0)
> INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
> SELECT Expire_Date FROM TJS
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "TJS" <nospam@.here.com> wrote in message
> news:1132gu53p4kt9bd@.corp.supernews.com...
>
|||hi,
TJS wrote:
> your example works, but I am trying to use a stored procedure
> I have this in the stored procedure:
> @.ExpireDate datetime = DEFAULT
> The column default value is set as (1/1/2020)
> but it still enters 1/1/1900
>
do you mean your procedure's code is
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = DEFAULT
INSERT INTO #test VALUES ( 1 , @.ExpireDate )
or
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = '20200101'
INSERT INTO #test VALUES ( 2 , @.ExpireDate )
?
the first code will actually raise an exception (Incorrect syntax near the
keyword 'DEFAULT'.) and non data will be entered...
can you please expand?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Alter PROCEDURE dbo.AddUser
(
@.Name nvarchar(50),
@.Email nvarchar(100),
@.Password nvarchar(50),
@.ExpireDate datetime = DEFAULT,
@.EnableNewsLetter bit,
@.UserID int OUTPUT
)
AS
INSERT INTO _Users
(
Name,
Email,
Password,
ExpireDate,
EnableNewsletter
)
VALUES
(
@.Name,
@.Email,
@.Password,
@.ExpireDate,
@.EnableNewsLetter
)
SELECT
@.UserID = @.@.Identity
|||hi TJS,
TJS wrote:
> Alter PROCEDURE dbo.AddUser
> (
> @.Name nvarchar(50),
> @.Email nvarchar(100),
> @.Password nvarchar(50),
> @.ExpireDate datetime = DEFAULT,
> @.EnableNewsLetter bit,
> @.UserID int OUTPUT
>.....
you can not use the DEFAULT keyword that way as you have to provide an
explicit default and not the "DEFAULT" keyword if you want it to be used for
not provided paramenter... that's to say you have perhaps to set it as
@.ExpireDate datetime = 'some date',
if you check your code, @.ExpireDate will always be NULL if not explicit
value has been specified for that parameter...
your code is like
SET NOCOUNT ON
GO
CREATE TABLE dbo._Users (
UserID int IDENTITY
, Name nvarchar (10) --(50)
, Email nvarchar (20) --(100)
, Password varchar(10) --(50)
, ExpireDate datetime DEFAULT '20050101'
, EnableNewsletter bit DEFAULT 0
)
GO
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = DEFAULT -- this value will never be used and the
underlaying
-- column default can not be used
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
-- SELECT @.ExpireDate always returns NULL if no explicit value is passed
INSERT INTO dbo._Users
(
Name
, Email
, Password
, ExpireDate
, EnableNewsletter
)
VALUES
(
@.Name
, @.Email
, @.Password
, @.ExpireDate
, @.EnableNewsLetter
)
SELECT @.UserID = SCOPE_IDENTITY()
GO
DECLARE @.UserId int
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
-- , @.ExpireDate -- param not provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = NULL -- param exlicitely NULL
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = '20050315' -- param provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
SELECT *
FROM dbo._Users
-- WHERE UserID = @.UserId
GO
DROP PROC dbo.AddUser
DROP TABLE dbo._Users
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna NULL
1 -- no value specified
2 Andrea andrea@.andrea.com aerdna NULL
1 -- explicit NULL specified
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
but modifyng the daclaration of the sp's parameters, providing an explicit
value for that parameter like
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = '20050101'
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
....
you will get a different result as
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna 2005-01-01
00:00:00.000 1
2 Andrea andrea@.andrea.com aerdna NULL
1
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
you can perhaps check your parameters like
IF ISNULL ( @.ExpireDate ) BEGIN
-- set it to whatever you want
END
or execute 2 different INSERT statements depending on the IF condition, ie:
do not provide the [ExpireDate] column if you want it to default to your
CREATE TABLE column default like
IF ISNULL ( @.ExpireDate ) BEGIN
INSERT INTO dbo._Users ( Name , Email , Password , EnableNewsletter )
VALUES ...
ELSE
INSERT INTO dbo._Users ( Name , Email , Password , ExpireDate ,
EnableNewsletter ) VALUES ...
but I'd better check for ISNULL and set it accordingly to your needs, as all
you other parameters should be checked as well
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more...
you can even query the INFORMATION_SCHEMA.COLUMNS ANSI view for columns
information like nullability and default to perform your own check and
eventual default settings...
SET NOCOUNT ON
CREATE TABLE dbo.Test (
ID int NOT NULL ,
dt datetime DEFAULT getdate()
)
GO
SELECT c.COLUMN_DEFAULT , c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'Test'
-- AND c.COLUMN_NAME = 'dt'
GO
DROP TABLE dbo.Test
--<--
COLUMN_DEFAULT IS_NULLABLE
-- --
NULL No
(getdate()) YES
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Default Contraint Problem

Hello,
I have 6 SQL Server in different locations running same applications. In a
table a column has a default contsraint, It works in 3 servers but the other
3 servers it does not work the column gets NULL value.
Any Idea?
Thanks in advance,
Erdal,Can you generate the SQL script for the table that works, the table that
doesn't work, and the insert statement used?
http://www.aspfaq.com/5006
"Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
news:eMosU6yoFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have 6 SQL Server in different locations running same applications. In
> a
> table a column has a default contsraint, It works in 3 servers but the
> other
> 3 servers it does not work the column gets NULL value.
> Any Idea?
> Thanks in advance,
>
> Erdal,
>
>|||This works
[CostV_LA] [numeric](21, 8) NULL CONSTRAINT [DF_tblOrderLines_CostV_LA]
DEFAULT (0),
This works too
[CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT (0),
This does not.
[CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT (0),
The strange thing the one that does not work today was working last month.
There is no sp updating this column.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23PM%23t9yoFHA.2080@.TK2MSFTNGP14.phx.gbl...
> Can you generate the SQL script for the table that works, the table that
> doesn't work, and the insert statement used?
> http://www.aspfaq.com/5006
>
>
> "Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
> news:eMosU6yoFHA.3552@.TK2MSFTNGP10.phx.gbl...
In
>|||> This works
> [CostV_LA] [numeric](21, 8) NULL CONSTRAINT [DF_tblOrderLines_CostV_LA]
> DEFAULT (0),
> This works too
> [CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT
> (0),
>
> This does not.
> [CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT
> (0),
Can you give the whole CREATE TABLE script? Also summarize any differences
between the two servers, e.g. @.@.version, regional settings, DBCC
USEROPTIONS. Also, if you don't want NULL to end up in the table, your
constraints should be:
[CostV_LA] [MONEY] NOT NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT
(0),
Do you see why it's impossible to diagnose the problem from here? Imagine
me telling you, I have two cars in my driveway, and one of them doesn't
work. What's the problem? You ask for more information. I tell you, well,
they're both Chevy Novas. Does that help?
Are you beating your head against a wall yet? I am!

> There is no sp updating this column.
There must be an INSERT statement that causes the default to either work or
not (if you do not insert data into the row, how do you know the constraint
fails to work?).
A

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

DEFAULT constraint not working

I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this

TRUNCATE TABLE NDRS_Call_Data_Table

INSERT INTO NDRS_Call_Data_Table(<column-list>)
SELECT <column-list> FROM NDRS_Call_Data_View

The view is a complex join of several other tables. When I run the proc, I get the error:

"Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."

BUT, if I simply do this from Query Analyzer.....

INSERT INTO NDRS_Calls_Data_Table(data_indicator)
VALUES('Z')

it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)

Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.|||Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks|||Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.|||Originally posted by cvandemaele
Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.

Well, forget about this last post. Just getting a cup of coffee and WHAM problem solved.|||Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT ... SELECT syntax|||To solve the problems of nulls in your view, you can use isnull(a, b) where a is the selected column and b is the default value for that column.

example:

select isnull (title, 'No title') as title, au_lname
from authors a left join titleauthor ta on a.au_id = ta.au_id left join titles t on t.title_id = ta.title_id|||True.

Undortunately, when using INSERT ... SELECT you can't say
SELECT ..., ISNULL(column, DEFAULT), ...

the way you can use the DEFAULT keyword like this...

INSERT
VALUES (x,y,z,DEFAULT,p,q,r)

You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.|||you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:

declare @.value int
select @.value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
from syscomments m,syscolumns c,sysobjects o
where c.id=object_id('dbo.your_table')
and c.name=('your_column_name')
and o.type='d'
and m.id=c.cdefault
and m.id=o.id

Default Column Values

I want to be able to set the default value of a column to be the next number
available, ie. max(MyColumn) + 1 (?).
Ordinarily, you would use an identity field for this, but a) we already have
one (primary key) and b) this value will possibly change such that several
rows will have the same MyColumn value.
Any suggestions?
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]You could either a) let your application control the insertion of data
(which is probaby the best solution because it simplifies the
validation of data on the data level) OR b) write a INSERT trigger to
find the max value and if this column is not specified, then insert the
business rule you specified.
I try to avoid triggers when I can, because I think it places a burden
on your database performance, and I do a lot of DTS bulk inserts (which
don't fire triggers by default).
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
> You could either a) let your application control the insertion of data
> (which is probaby the best solution because it simplifies the
> validation of data on the data level) OR b) write a INSERT trigger to
> find the max value and if this column is not specified, then insert the
> business rule you specified.
> I try to avoid triggers when I can, because I think it places a burden
> on your database performance, and I do a lot of DTS bulk inserts (which
> don't fire triggers by default).
>
So the formula can't be used in the columns Default Value property?
Why would a simple trigger like that burden the server any more than an
extra query to the Db to determine the appropriate value? I'm not
disagreeing with you, I'm just curious...
Server load is not such a big issue for me, but then again, that's not
really a reason to ignore it...
Chris|||You could write a function that returns the max + 1, and use it as a default
value, but this solution will have issues:
create function dbo.fn_nextkey() returns int
as
begin
return coalesce((select max(keycol) + 1 from t1), 1);
end
go
create table t1
(
keycol int not null primary key default dbo.fn_nextkey(),
datacol varchar(10) not null
);
go
insert into t1(datacol) values('a');
insert into t1(datacol) values('b');
insert into t1(datacol) values('c');
select * from t1;
keycol datacol
-- --
1 a
2 b
3 c
Multiple processes inserting at the same time will get the same value, and
you will get pk violation errors that you'd need to trap and handle.
A better option would be to create a table that maintains the last assigned
value:
create table seq(val int not null);
insert into seq values(0);
And increment the value every time you need a new key using a stored
procedure:
create proc usp_nextkey @.o as int output
as
update seq set @.o = val = val + 1;
go
When you need a new key, invoke the proc as follows:
declare @.i as int;
exec usp_nextkey @.i output;
insert into t1 values(@.i, 'd');
BG, SQL Server MVP
www.SolidQualityLearning.com
"CJM" wrote:

> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
>
> So the formula can't be used in the columns Default Value property?
> Why would a simple trigger like that burden the server any more than an
> extra query to the Db to determine the appropriate value? I'm not
> disagreeing with you, I'm just curious...
> Server load is not such a big issue for me, but then again, that's not
> really a reason to ignore it...
> Chris
>
>|||> (which is probaby the best solution because it simplifies the
> validation of data on the data level)
In general, unless you have a magical way of preventing users from accessing
the data *except* through your application, there is no good place to put
data validation *except* in the data layer. YMMV.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> In general, unless you have a magical way of preventing users from
> accessing the data *except* through your application, there is no good
> place to put data validation *except* in the data layer. YMMV.
>
Aaron,
I agree with you here... So out of interest, would you calculate the next
value within the same SP that inserts the row, or would you us a trigger?
(or another alternative?)
Chris|||I guess it's a matter of scale; we tend to insert a lot of data at one
time, and I try to minimize the queries to my database as much as
possible. In this particular case, the trigger would not be onerous,
but I've seen some really, really bad triggers written that can suck
the life out a server. I just tend to avoid them; not that they're
always bad, but in most of our applications we try to have the data be
as clean as possible before inserting it into the database. In other
words, we do all the lookups and data prep on the business logic layer,
not in the database.
Again, it's a matter of scale; we insert a lot of data at a very high
rate of speed; the simpler the INSERT process is, the better.
Stu|||Personally, I like Itzik's solution, I just kind of cringe a bit at the
syntax:
update table set @.variable = column = column + 1;
But that's just a minor pet peeve I guess.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OSaS2Z$oFHA.568@.TK2MSFTNGP10.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> Aaron,
> I agree with you here... So out of interest, would you calculate the next
> value within the same SP that inserts the row, or would you us a trigger?
> (or another alternative?)
> Chris
>|||no magic; we just lock our data servers down pretty tight, using
application roles, etc. You are correct in that someone could bypass
our application, but we do our best to limit that possibility.
As far as validation goes, I agree. I'm just saying that validation
should be as simple as possible on the database level (e.g., is the
value with constrained parameters? Does it exist in a relationship
with other values?), and that more complex permutations should be
assigned at the business tier level before it gets written to the
database.
Stu
PS: in my previous posts, I used the term application in a broad sense,
encompassing both presentation and business logic tiers. Just wanted
to clarify.

default column value

as current time..
how can i set that in mssql 2005 ?Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
CREATE TABLE test_defaults
(keycol smallint,
process_id smallint DEFAULT @.@.SPID, --Preferred default definition
date_ins datetime DEFAULT getdate(), --Preferred default definition
mathcol smallint DEFAULT 10 * 2, --Preferred default definition
char1 char(3),
char2 char(3) DEFAULT 'xyz') --Preferred default definition;
GO
HTH. Ryan
"gary" <admin@.newsgroup.com.hk> wrote in message
news:eHFDHarEGHA.3100@.tk2msftngp13.phx.gbl...
> as current time..
> how can i set that in mssql 2005 ?
>|||Note that naming constraints is considered good practice. Sooner or later yo
u will want to change
some defaults, and if you don't know the name, you have to look up the auto-
generated name in the
system tables. This makes implementation of such scripts a mess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uwM3JhrEGHA.916@.TK2MSFTNGP10.phx.gbl...
> Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
> CREATE TABLE test_defaults
> (keycol smallint,
> process_id smallint DEFAULT @.@.SPID, --Preferred default definition
> date_ins datetime DEFAULT getdate(), --Preferred default definition
> mathcol smallint DEFAULT 10 * 2, --Preferred default definition
> char1 char(3),
> char2 char(3) DEFAULT 'xyz') --Preferred default definition;
> GO
>
> --
> HTH. Ryan
>
> "gary" <admin@.newsgroup.com.hk> wrote in message news:eHFDHarEGHA.3100@.tk2
msftngp13.phx.gbl...
>

Default column causing problems!

Hi everybody,

Iam migrating a table called Vendors from sql 2005 to flatfile.

but it end with error message that the default column is causing problem.

the table is as follows,

CREATE TABLE VENDORS

(

RECORDTYPECHAR(5)DEFAULT'VNDRS' NULL,

SETIDCHAR(5)NOT NULL,

VENDORIDCHAR(10)NOT NULL,

VENDORNAMESHORTCHAR(14)NOT NULL,

VENDORNAMESEQNUMINTNULL,

NAME1CHAR(40)NOT NULL,

NAME2CHAR(40)NULL,

REMITVENDORCHAR(10)NULL,

CUSTSETIDCHAR(5)NULL,

CUSTIDCHAR(15)NULL,

ENTEREDBYCHAR(8)NULL,

ARNUMCHAR(15)NULL,

OLDVENDORIDCHAR(15)NULL,

WTHDSWCHAR(1)NOT NULL,

VATSWCHAR(1)NOT NULL,

NAME1ACCHAR(40)NULL,

NAME2ACCHAR(40)NULL,

PRIMARYVENDORCHAR(10)NULL,

LASTACTIVITYDTDATETIMENULL,

HUBZONECHAR(1)NOT NULL,

EEOCERTIFDTDATETIMENULL,

VENDORAFFILIATECHAR(5)NULL

)

any idea as what i need to do?

pls help out.

Thanks and Regards,

sg

You haven't told us anything. What's the error and what are you doing to get that error?|||Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1|||

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

|||

Rafael Salas wrote:

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

Thanks! You're the man.|||

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

|||

swan_sg wrote:

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

Please don't summarize the error in your own words. Please copy and paste the error here. Also, how do you have your data flow setup? Please identify all of the steps and what components you have in your data flow.

Thanks,

Phil

default column causing problem!

Hi everybody,

Iam migrating a table called Vendors from sql 2005 to flatfile.

but it end with error message that the default column is causing problem.

the table is as follows,

CREATE TABLE VENDORS

(

RECORDTYPECHAR(5)DEFAULT'VNDRS' NULL,

SETIDCHAR(5)NOT NULL,

VENDORIDCHAR(10)NOT NULL,

VENDORNAMESHORTCHAR(14)NOT NULL,

VENDORNAMESEQNUMINTNULL,

NAME1CHAR(40)NOT NULL,

NAME2CHAR(40)NULL,

REMITVENDORCHAR(10)NULL,

CUSTSETIDCHAR(5)NULL,

CUSTIDCHAR(15)NULL,

ENTEREDBYCHAR(8)NULL,

ARNUMCHAR(15)NULL,

OLDVENDORIDCHAR(15)NULL,

WTHDSWCHAR(1)NOT NULL,

VATSWCHAR(1)NOT NULL,

NAME1ACCHAR(40)NULL,

NAME2ACCHAR(40)NULL,

PRIMARYVENDORCHAR(10)NULL,

LASTACTIVITYDTDATETIMENULL,

HUBZONECHAR(1)NOT NULL,

EEOCERTIFDTDATETIMENULL,

VENDORAFFILIATECHAR(5)NULL

)

any idea as what i need to do?

pls help out.

Thanks and Regards,

sg

You haven't told us anything. What's the error and what are you doing to get that error?|||Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1|||

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

|||

Rafael Salas wrote:

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

Thanks! You're the man.|||

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

|||

swan_sg wrote:

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

Please don't summarize the error in your own words. Please copy and paste the error here. Also, how do you have your data flow setup? Please identify all of the steps and what components you have in your data flow.

Thanks,

Phil

sql

Default charset in sqlserver 2000? / jtds

Hi,

I am writing to a text column in my SQL Server 2000 database. The text
comes from a web form in my java web application, where the character
encoding is ISO-8859-1. (I have no control over the charset, my app is
a plugin inside another app.)
Characters such as (ascii 128) and '(ascii 146) are inserted into
the db as '?'.

I'm connecting using the free jtds driver, and I'm not specifying any
details about charsets in my usage of the driver.

Can anyone tell me what the default charset in sqlserver 2000 is?
Should I be specifying this charset when using my driver?
Thanks.downlode@.gmail.com wrote:
> Hi,
> I am writing to a text column in my SQL Server 2000 database. The text
> comes from a web form in my java web application, where the character
> encoding is ISO-8859-1. (I have no control over the charset, my app is
> a plugin inside another app.)
> Characters such as (ascii 128) and '(ascii 146) are inserted into
> the db as '?'.
> I'm connecting using the free jtds driver, and I'm not specifying any
> details about charsets in my usage of the driver.
> Can anyone tell me what the default charset in sqlserver 2000 is?
> Should I be specifying this charset when using my driver?
> Thanks.

You probably want to use ntext instead of text, nvarchar instead of
varchar, etc.|||(downlode@.gmail.com) writes:
> I am writing to a text column in my SQL Server 2000 database. The text
> comes from a web form in my java web application, where the character
> encoding is ISO-8859-1. (I have no control over the charset, my app is
> a plugin inside another app.)
> Characters such as ?(ascii 128) and '(ascii 146) are inserted into
> the db as '?'.

Hm, in iso-8859-1, the slots 128-159 not graphic characters. In Windows-
1252, Microsoft's extension of 8859-1, some of them are indeed graphic.

> I'm connecting using the free jtds driver, and I'm not specifying any
> details about charsets in my usage of the driver.
> Can anyone tell me what the default charset in sqlserver 2000 is?

No, because this depends on the regional settings of the machine. For
instance, if I install SQL Server on my machine, and do not make any
selection, I will get Finnish_Swedish_CI_AS, which implies code page
1252. People in Poland are likely to get Polish_CI_AS, which implies
code page 1250. And that's only the default. This can be overridden
at installation. And then the collation can be set independently by
column.

So start doing

SELECT serverproperty('Collation') -- Server default collation.
SELECT databasepropertyex('db', 'Collation') -- Database default

And then use sp_help to determine the coilations of the columns you
are working with. If you don't know which code page a certain collation
has, there is a function Collationproperty() for this.

If the columns are of different code pages, you will have to use
Unicode somewhere on the way, and as Trevor said, ntext nvarchar are
probably better options.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
sorry for the late follow up to this.
My database has the same collation throughout -
SQL_Latin1_General_CP1_CI_AS
The columns share this collation.
Even when I use a preparedStatement, ensuring that the outgoing text is
treated as Unicode by the free jtds driver, I get the same problems.

I am stumped by this one.

If I change my text column to an ntext column, will this affect the
existing entries?
Thanks,
Mike|||(downlode@.gmail.com) writes:
> sorry for the late follow up to this.
> My database has the same collation throughout -
> SQL_Latin1_General_CP1_CI_AS
> The columns share this collation.
> Even when I use a preparedStatement, ensuring that the outgoing text is
> treated as Unicode by the free jtds driver, I get the same problems.
> I am stumped by this one.

Since SQL_Latin1_General_CP1_CI_AS is share code page with iso-8859-1,
it's indeed a little funny. But as I noted in my previous post, the
characters you have problem with are not in iso-8859-1 - these code
points are control characters to 8859-1. In Windows Latin-1 they are
indeed printable characters.

My guess is that the free jtds takes a strict definiton of what is
8859-1. But I don't it, so you should inquire in a forum for that driver.

> If I change my text column to an ntext column, will this affect the
> existing entries?

No.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Default additional field to identity value?

I've got a record that has an identity column and secondary identifier that I need to have default to the same value as the identity column:

id int identity(1,1) not null ,
name varchar(20) not null default CAST($IDENTITY AS VARCHAR(20))

Problem is, using @.@.identity or scope_identity() as the default for name gives me the prior insert's identity value, not the current record's value. Using an AFTER trigger doesn't work because the initial insert fails due to the not null constraint, and using an INSTEAD OF trigger does not work because the identity value is not set on the inserted row.

Is there any way to set a not-null field on a record equal to the identity value assigned to the record?

There is no way to do this declaratively. You can use a computed column instead of a persisted column if the name column is just string representation of the identity value. Do you allow the name value to be modified later? If so then you will have to use a trigger to update the value and set default to 0 or -1.sql

Decryption within an application

I need to encrypt one column of data in a single table and I pretty much
have all the operations figured out, including maintaining both the
encrpyted data and a one way hash for searches. I have a view which
decrypts the data properly when the symmetric key has been opened (and
obviously returns null when the key is not open).
I want the view to return the decrypted data only when the user is accessing
the database from a single application. This application maintains a single
database connection per session. My thought was to open the key when the
database connection is established by the application and close it when the
application exits, thereby granting access only through the application. Is
that an acceptable practice?
If I do that, should I protect the key with a password that is then compiled
in the application so that I can open the key? This means that every
installation will have a key protected by the same password. Or is there a
better way to do that?
Thanks for any help."Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
>I need to encrypt one column of data in a single table and I pretty much
>have all the operations figured out, including maintaining both the
>encrpyted data and a one way hash for searches. I have a view which
>decrypts the data properly when the symmetric key has been opened (and
>obviously returns null when the key is not open).
> I want the view to return the decrypted data only when the user is
> accessing the database from a single application. This application
> maintains a single database connection per session. My thought was to
> open the key when the database connection is established by the
> application and close it when the application exits, thereby granting
> access only through the application. Is that an acceptable practice?
> If I do that, should I protect the key with a password that is then
> compiled in the application so that I can open the key? This means that
> every installation will have a key protected by the same password. Or is
> there a better way to do that?
> Thanks for any help.
Well, when the key is opened it's specific to a session. So you could have
several sessions opening up the same key simultaneously and I wouldn't think
you'd encounter any problems. Of course you will probably want to do some
thorough testing to be sure, and also make sure you don't take a performance
hit there. I wouldn't recommend storing the key hard-coded in your
application. How about using the Automatic Key Management feature of SQL
2005? The only real downside to it is that all sysadmins can then decrypt
your data (if that's a concern for you - it is for some folks).|||I'm not neccesarily opposed to the sysadmin being able to decrypt the data,
but I don't want any other user outside of the application to have access to
the data. So what I can't figure out (even with SS key management) is how
to open the key only when connecting from the application, unless I compile
a password into the code.
Any thoughts on that would be helpful.
Thanks.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Well, when the key is opened it's specific to a session. So you could
> have several sessions opening up the same key simultaneously and I
> wouldn't think you'd encounter any problems. Of course you will probably
> want to do some thorough testing to be sure, and also make sure you don't
> take a performance hit there. I wouldn't recommend storing the key
> hard-coded in your application. How about using the Automatic Key
> Management feature of SQL 2005? The only real downside to it is that all
> sysadmins can then decrypt your data (if that's a concern for you - it is
> for some folks).
>|||With automatic key management you should be able to connect to the
application and open the symmetric keys without a password. You can use
GRANT to grant permissions to users on your keys, certificates, etc. Here's
an article with some samples that demonstrate encryption/decryption without
passwords, thanks to automatic key management:
[url]http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp[/
url]
You might also want to look into the DecryptByKeyAutoAsymKey and
DecryptByKeyAutoCert functions that combine the DecryptBy... functions with
OPEN SYMMETRIC KEY automatically.
"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
> I'm not neccesarily opposed to the sysadmin being able to decrypt the
> data, but I don't want any other user outside of the application to have
> access to the data. So what I can't figure out (even with SS key
> management) is how to open the key only when connecting from the
> application, unless I compile a password into the code.
> Any thoughts on that would be helpful.
> Thanks.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
>|||Thanks so much for your help, but I must be dense. If I grant permission to
the users or use automatic key management, then it seems to me that the data
can be encrypted outside of my application by a non-sa user. If I only want
the application to display the unencrypted data, I can't see how this
automatic approach works.
That is why I took the approach of having the application open the key.
Sort of like the old application-role security.
But I would love to find a better way.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Od0T8%23amHHA.4772@.TK2MSFTNGP05.phx.gbl...
> With automatic key management you should be able to connect to the
> application and open the symmetric keys without a password. You can use
> GRANT to grant permissions to users on your keys, certificates, etc.
> Here's an article with some samples that demonstrate encryption/decryption
> without passwords, thanks to automatic key management:
> http://www.sqlservercentral.com/col...ion.asp

> You might also want to look into the DecryptByKeyAutoAsymKey and
> DecryptByKeyAutoCert functions that combine the DecryptBy... functions
> with OPEN SYMMETRIC KEY automatically.
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23C1qbs$mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Thanks so much for your help, but I must be dense. If I grant permission
> to the users or use automatic key management, then it seems to me that the
> data can be encrypted outside of my application by a non-sa user. If I
> only want the application to display the unencrypted data, I can't see how
> this automatic approach works.
Anyone who has the username and password used by the application to log into
the database would have the ability to decrypt the encrypted data.
Alternatively, if a password is stored in the application, anyone with a hex
editor could decrypt the encrypted data outside of the application. The
only way I can think of to force decryption only through the application
would be to encrypt only in the application. But then you take on the
responsibility of encryption key management yourself. I don't know of any
magic bullet to ensure that data encrypted using SQL Server can only be
accessed via a specific front-end application interface.

> That is why I took the approach of having the application open the key.
> Sort of like the old application-role security.
Anyone with a hex editor could conceivably locate a password stored in an
application and use it to decrypt data. Linking it to a specific Windows
login puts the burden of encryption key management back on the operating
system. That's basically the main difference; do you want to manage your
own passwords, or do you want to let Windows and SQL Server manage your
passwords?

> But I would love to find a better way.
Biometrics?

Decryption Question

Hi,

I am encrypting a column in a table. When I decrypt, everything is great if the data contained in column is less than 30 chars. If it has more than 30 chars, I still get 30 chars decrypted. Not sure what I am doing wrong. Below are my details:

OPEN SYMMETRIC KEY sk_XEncryption
DECRYPTION BY CERTIFICATE cert_sk_X

SELECT ISNULL(CONVERT(NVARCHAR,DECRYPTBYKEY(columnA)),'')
FROM table1

CLOSE SYMMETRIC KEY sk_XEncryption

Additional details:

"columnA" above is of VARBINARY(4000) datatype. I have enven tried changing the column to VARBINARY(MAX). The data is ecrypted with a symmetric key (algorithm = triple_des ). Any tips are appreciated. TIA.

Does it help if you specify the length for the NVARCHAR output?
For example:
SELECT ISNULL(CONVERT(NVARCHAR(255),DECRYPTBYKEY(columnA)),'')
FROM table1
|||That helped!!! Thanks a lot.

Decrypting returns a NULL value

I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:

Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value

Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'

-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go

-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO

-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';

-- I then verified if the key is opened
SELECT * FROM sys.openkeys

If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!

Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.

Thanks in advance!

faiga16

3 Posts

I wish I could be more helpful, but all I have to offer is that I think it has to do with your Server master key being different. I was just at a conference where this exact scenario was mentioned and there is a way to use the password (or whatever it is called) to re-encrypt the restored database with the master key so that you can decrypt it.

[ed] sorry it looks like I missed the end of your post. It looks like you're already headed down the path I suggested.

|||

If the two servers are the same OS then I think all you will need to do is run:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

to the new server.

hth,

-Steven Gott

S/DET

SQL Server

|||

Hi Steve,

Yes they have the same OS and SQL version. But executing

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

doesnt work too... it prompt an error saying "Create a master key in the database or open the master key in the session before performing this operation"

Is my procedure correct base on what you suggest?

1) restored the Database1

2) run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

I am getting to know my problem.

Before the alter master key command I opened the master key by open master key decryption by password command. Now it prompts an error that the decryption password I supplied was incorrect. Is there a way that I could know the master key encryption/decryption password? I just got here with all the encryption/decryption set up. Nobody remember the master key password duh!

|||

They are quite sure the master key they gave me was the one they used before.

"The key is not decrypted using the specified decryptor"

|||

From the error you are getting back, it seems like the password to decrypt the master key (DBMK) is not correct. The passwords are case sensitive; make sure it is typed exactly as it was originally created.

If you have a backup of the DBMK, you can try restoring the backup on top of the current copy (it should be the same DBMK) using the RESTORE MASTER KEY … FORCE statement.

NOTE: As you are using the force option, make sure you have a copy of the original DB in case the DBMK from the backup doesn’t match the one in your DB.

If the original server still exists, there may be one possibility: you can try to restore the DB on the original server and see if you can still access the DBMK based on the original service master key encryption. From there you can try to regenerate the DBMK (ALTER MASTER KEY REGENERATE) to establish a new DBMK password. I am not sure if this one will work, but it may be worth trying it.

I hope this helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks! Will test if this would work...

Decrypting from replicated table

I have a replicated table with an encrypted SSN column. In the source
database, we have created the master key, certificate, and symmetric key. I
s
there a way for me to decrypt the SSN from the replicated table, or do I hav
e
to perform a distributed query to the source database in order to get the
decrypted SSN?You can do a distributed query or you can setup the same master key,
certificate, and symmetric key on the subscriber.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:4EF93674-2A25-441B-BDA2-BE3261AC55D1@.microsoft.com...
>I have a replicated table with an encrypted SSN column. In the source
> database, we have created the master key, certificate, and symmetric key.
> Is
> there a way for me to decrypt the SSN from the replicated table, or do I
> have
> to perform a distributed query to the source database in order to get the
> decrypted SSN?|||How can I create the same keys in a different database?
"Michael Hotek" wrote:

> You can do a distributed query or you can setup the same master key,
> certificate, and symmetric key on the subscriber.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Arnel" <Arnel@.discussions.microsoft.com> wrote in message
> news:4EF93674-2A25-441B-BDA2-BE3261AC55D1@.microsoft.com...
>
>|||Use the KEY_SOURCE and the IDENTITY_VALUE parameters of CREATE SYMMETRIC
KEY. For additional information, see
http://msdn2.microsoft.com/en-us/library/ms188357.aspx and the last section
of this post: http://blogs.msdn.com/lcris/archive...14/481434.aspx.
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.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:03E00687-3EB6-4182-AEE3-94C4BC3DA0F7@.microsoft.com...[vbcol=seagreen]
> How can I create the same keys in a different database?
> "Michael Hotek" wrote:
>|||Thank you for the clarification. However, please correct my understanding
here:
1. I will need to drop my existing, original symmetric key and recreate
using the KEY_SOURCE and IDENTITY_VALUE params.
2. I will then need to "re-encrypt" my data using that new symmetric key
3. In the database, with the replicated table, I will need to create a new
symmetric key using the same params from the original key.
Please clarify any misunderstanding I have about the process. Thanks.
"Laurentiu Cristofor [MSFT]" wrote:

> Use the KEY_SOURCE and the IDENTITY_VALUE parameters of CREATE SYMMETRIC
> KEY. For additional information, see
> http://msdn2.microsoft.com/en-us/library/ms188357.aspx and the last sectio
n
> of this post: http://blogs.msdn.com/lcris/archive...14/481434.aspx.
> --
> 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
.
> "Arnel" <Arnel@.discussions.microsoft.com> wrote in message
> news:03E00687-3EB6-4182-AEE3-94C4BC3DA0F7@.microsoft.com...
>
>|||Don't drop the original symmetric key before re-encrypting, you will need it
to decrypt the existing data. Otherwise, this will be indeed the process:
create new key using the params, decrypt with old key and reencrypt with the
new one, then in the replicated database recreate the key from the same
params.
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.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:A68460ED-9912-40AC-9788-9A1C5E2FD15E@.microsoft.com...[vbcol=seagreen]
> Thank you for the clarification. However, please correct my understanding
> here:
> 1. I will need to drop my existing, original symmetric key and recreate
> using the KEY_SOURCE and IDENTITY_VALUE params.
> 2. I will then need to "re-encrypt" my data using that new symmetric key
> 3. In the database, with the replicated table, I will need to create a
> new
> symmetric key using the same params from the original key.
> Please clarify any misunderstanding I have about the process. Thanks.
> "Laurentiu Cristofor [MSFT]" wrote:
>

DecryptByPassPhrase not decrypting varchar columns after copying a database

I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.

Also this same code works correctly and as expected by decrypting the encrypted fields in production.|||

A couple of observations first:

(1) Why are you converting the columns to varchar(max)? That should not be necessary.
(2) What is the reason you are using the "1, convert(varbinary, 23)" arguments? I don't see how those could be helpful.

From you description, it appears that the data was mangled during the transfer. What is the type of the encrypted columns?

Also, given that this appears to be a copy database problem, your question may be better directed to the SQL Server Tools General forum. If the column contains a different value after the copy, the decryption is expected to fail.

Thanks
Laurentiu

|||

Mr. Cistofor,

1. It made sense at the time.
2. I must have added that to confuse myself and others later on (job security or bad programming - you decide).
3. Data mangled in transfer, yep thats what it be.
4. Wrong msg board. Sorry, I was in a hurry and I used writting the post as a way to think it through instead of prepping more before submission. My bad dog!

Thanks for the time, consideration and consultation,

Mike512

|||

Ok, you are of course free to write code as you wish, but I wanted to point out that you are doing unnecessary operations. (1) may not be costly, but for (2) you are forcing an additional hash computation per value, which is expensive and will degrade the performance of your queries - it also doesn't serve much purpose from a security point of view.

Thanks
Laurentiu

sql

Decrypt permissions?

Howdy all. I just did the BOL example of encrypting a column of data:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
6cda0adc.htm
and it worked great. However, Ive been trying to figure out how to let an
end user (someone with just read permissions) decrypt the data for a while
now to no avail. Can someone please assist? Also, I had to use "
WITH ALGORITHM = DES"
instead of what BOL said to to my version of SQL Server.
TIA, ChrisRChrisR wrote:
> Howdy all. I just did the BOL example of encrypting a column of data:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
> 6cda0adc.htm
> and it worked great. However, Ive been trying to figure out how to let an
> end user (someone with just read permissions) decrypt the data for a while
> now to no avail. Can someone please assist? Also, I had to use "
> WITH ALGORITHM = DES"
> instead of what BOL said to to my version of SQL Server.
> TIA, ChrisR
The end user can access the data through a stored proc that decrypts
the data for him. Stored procs are the preferred method for data access
whether or not the data is encrypted. Execute permission is all that
the user will need.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you David. Do you happen to know if it can be done without an SP or
not? We replicate a lot of data to a "reporting box", with the sole purpose
in mind of developers and power users being able to query their own data.
Thanks again.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1164063291.160874.187260@.b28g2000cwb.googlegroups.com...
> ChrisR wrote:
> > Howdy all. I just did the BOL example of encrypting a column of data:
> >
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
> > 6cda0adc.htm
> >
> > and it worked great. However, Ive been trying to figure out how to let
an
> > end user (someone with just read permissions) decrypt the data for a
while
> > now to no avail. Can someone please assist? Also, I had to use "
> > WITH ALGORITHM = DES"
> >
> > instead of what BOL said to to my version of SQL Server.
> >
> > TIA, ChrisR
> The end user can access the data through a stored proc that decrypts
> the data for him. Stored procs are the preferred method for data access
> whether or not the data is encrypted. Execute permission is all that
> the user will need.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||The whole ide of using an SP is to provide access to the data without
granting permissions to the base table. Can you avoid using an SP? Sure but
that means you will end up providing access to the encryption key and table
which is usually not a good thing to do if you are really concerned about
security.
You could tweak the SP to have parameter for the calling userID (assuming
you have some way to determine the rows for "permitted" users) and filter
the results with a where clause but that can be a sizeable perf hit if
you're dealing with large tables and/or many concurrent users. If they don't
mind since it's a reporting server, lucky you.
joe.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OVIC2SRDHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Thank you David. Do you happen to know if it can be done without an SP or
> not? We replicate a lot of data to a "reporting box", with the sole
> purpose
> in mind of developers and power users being able to query their own data.
> Thanks again.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1164063291.160874.187260@.b28g2000cwb.googlegroups.com...
>> ChrisR wrote:
>> > Howdy all. I just did the BOL example of encrypting a column of data:
>> >
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
>> > 6cda0adc.htm
>> >
>> > and it worked great. However, Ive been trying to figure out how to let
> an
>> > end user (someone with just read permissions) decrypt the data for a
> while
>> > now to no avail. Can someone please assist? Also, I had to use "
>> > WITH ALGORITHM = DES"
>> >
>> > instead of what BOL said to to my version of SQL Server.
>> >
>> > TIA, ChrisR
>> The end user can access the data through a stored proc that decrypts
>> the data for him. Stored procs are the preferred method for data access
>> whether or not the data is encrypted. Execute permission is all that
>> the user will need.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>|||Thanks Joe. I understand the importance of security, but (unfortunately) the
powers that be want to be able to directly query their data. You brought up
a good point though that I hadn't thought of, passing out the encryption key
is a strong arguement, and won I can probably win.
Thanks!
"Joe Yong" <NO_jyong@.SPAM_scalabilityexperts.com> wrote in message
news:excTblRDHHA.4404@.TK2MSFTNGP03.phx.gbl...
> The whole ide of using an SP is to provide access to the data without
> granting permissions to the base table. Can you avoid using an SP? Sure
but
> that means you will end up providing access to the encryption key and
table
> which is usually not a good thing to do if you are really concerned about
> security.
> You could tweak the SP to have parameter for the calling userID (assuming
> you have some way to determine the rows for "permitted" users) and filter
> the results with a where clause but that can be a sizeable perf hit if
> you're dealing with large tables and/or many concurrent users. If they
don't
> mind since it's a reporting server, lucky you.
>
> joe.
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:OVIC2SRDHHA.4832@.TK2MSFTNGP06.phx.gbl...
> > Thank you David. Do you happen to know if it can be done without an SP
or
> > not? We replicate a lot of data to a "reporting box", with the sole
> > purpose
> > in mind of developers and power users being able to query their own
data.
> >
> > Thanks again.
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:1164063291.160874.187260@.b28g2000cwb.googlegroups.com...
> >> ChrisR wrote:
> >> > Howdy all. I just did the BOL example of encrypting a column of data:
> >> >
> >
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
> >> > 6cda0adc.htm
> >> >
> >> > and it worked great. However, Ive been trying to figure out how to
let
> > an
> >> > end user (someone with just read permissions) decrypt the data for a
> > while
> >> > now to no avail. Can someone please assist? Also, I had to use "
> >> > WITH ALGORITHM = DES"
> >> >
> >> > instead of what BOL said to to my version of SQL Server.
> >> >
> >> > TIA, ChrisR
> >>
> >> The end user can access the data through a stored proc that decrypts
> >> the data for him. Stored procs are the preferred method for data access
> >> whether or not the data is encrypted. Execute permission is all that
> >> the user will need.
> >>
> >> --
> >> David Portas, SQL Server MVP
> >>
> >> Whenever possible please post enough code to reproduce your problem.
> >> Including CREATE TABLE and INSERT statements usually helps.
> >> State what version of SQL Server you are using and specify the content
> >> of any error messages.
> >>
> >> SQL Server Books Online:
> >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> >> --
> >>
> >
> >
>|||Windows 200 Pro SP4.
SQL 2005 SP1.
I can get the decryption to work as me, but if I open up a new connection
with my test user, it returns NULL values instead of the decrypted data.
This is even if I create a proc and grant exec rights to the test user. Here
is precisely what I did:
USE AdventureWorks;
GO
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD ='23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Can someone please assist?
TIA, ChrisR|||You're missing the "EXECUTE AS OWNER" or execute as something-or-other
clause in your create procedure statement. If you don't have that clause, it
will default to execute as caller which means any user executing that sproc
will be checked for permissions and if it isn't granted, you get null values
in your SELECT.
joe.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:ONry7jYDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> Windows 200 Pro SP4.
> SQL 2005 SP1.
> I can get the decryption to work as me, but if I open up a new connection
> with my test user, it returns NULL values instead of the decrypted data.
> This is even if I create a proc and grant exec rights to the test user.
> Here
> is precisely what I did:
>
> USE AdventureWorks;
> GO
>
> --If there is no master key, create one now
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD => '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117
> r$$#1946kcj$n44ncjhdlj'
> GO
>
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
>
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
>
> USE [AdventureWorks];
> GO
>
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
>
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
>
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
>
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
>
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
>
> /*works for me*/
> exec getDecryptedIDNumber
>
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
>
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
>
> /*Now, open up a "file/new/DB Engine Query" and login with the test
> login*/
>
> exec getDecryptedIDNumber
>
> /*This returns NULL values where it should show the decrypted data*/
>
> Can someone please assist?
>
> TIA, ChrisR
>
>|||Thanks Joe, but I just tried:
ALTER procedure [dbo].[getDecryptedIDNumber]
with execute as 'dbo'
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
use [AdventureWorks]
GO
GRANT IMPERSONATE ON USER::[dbo] TO [test]
GO
exec as user = 'dbo'
exec getDecryptedIDNumber
and it still comes back as NULL. Any other ideas?
"Joe Yong" <NO_jyong@.SPAM_scalabilityexperts.com> wrote in message
news:ONp1UqeDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> You're missing the "EXECUTE AS OWNER" or execute as something-or-other
> clause in your create procedure statement. If you don't have that clause,
it
> will default to execute as caller which means any user executing that
sproc
> will be checked for permissions and if it isn't granted, you get null
values
> in your SELECT.
>
> joe.
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:ONry7jYDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> > Windows 200 Pro SP4.
> > SQL 2005 SP1.
> >
> > I can get the decryption to work as me, but if I open up a new
connection
> > with my test user, it returns NULL values instead of the decrypted data.
> > This is even if I create a proc and grant exec rights to the test user.
> > Here
> > is precisely what I did:
> >
> >
> > USE AdventureWorks;
> >
> > GO
> >
> >
> >
> > --If there is no master key, create one now
> >
> > IF NOT EXISTS
> >
> > (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> >
> > CREATE MASTER KEY ENCRYPTION BY
> >
> > PASSWORD => >
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117
> > r$$#1946kcj$n44ncjhdlj'
> >
> > GO
> >
> >
> >
> > CREATE CERTIFICATE HumanResources037
> >
> > WITH SUBJECT = 'Employee Social Security Numbers';
> >
> > GO
> >
> >
> >
> > CREATE SYMMETRIC KEY SSN_Key_01
> >
> > WITH ALGORITHM = DES
> >
> > ENCRYPTION BY CERTIFICATE HumanResources037;
> >
> > GO
> >
> >
> >
> > USE [AdventureWorks];
> >
> > GO
> >
> >
> >
> > -- Create a column in which to store the encrypted data
> >
> > ALTER TABLE HumanResources.Employee
> >
> > ADD EncryptedNationalIDNumber varbinary(128);
> >
> > GO
> >
> >
> >
> > -- Open the symmetric key with which to encrypt the data
> >
> > OPEN SYMMETRIC KEY SSN_Key_01
> >
> > DECRYPTION BY CERTIFICATE HumanResources037;
> >
> >
> >
> > -- Encrypt the value in column NationalIDNumber with symmetric
> >
> > -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> >
> > UPDATE HumanResources.Employee
> >
> > SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> > NationalIDNumber);
> >
> > GO
> >
> >
> >
> > -- Verify the encryption.
> >
> > -- First, open the symmetric key with which to decrypt the data
> >
> > OPEN SYMMETRIC KEY SSN_Key_01
> >
> > DECRYPTION BY CERTIFICATE HumanResources037;
> >
> > GO
> >
> >
> >
> > -- Now list the original ID, the encrypted ID, and the
> >
> > -- decrypted ciphertext. If the decryption worked, the original
> >
> > -- and the decrypted ID will match.
> >
> > create procedure getDecryptedIDNumber
> >
> > as
> >
> > SELECT NationalIDNumber, EncryptedNationalIDNumber
> >
> > AS "Encrypted ID Number",
> >
> > CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> >
> > AS "Decrypted ID Number"
> >
> > FROM HumanResources.Employee;
> >
> > GO
> >
> >
> >
> > /*works for me*/
> >
> > exec getDecryptedIDNumber
> >
> >
> >
> > USE [master]
> >
> > GO
> >
> > CREATE LOGIN [test] WITH PASSWORD=N'test',
> > DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
> >
> > GO
> >
> > USE [AdventureWorks]
> >
> > GO
> >
> > CREATE USER [test] FOR LOGIN [test]
> >
> > GO
> >
> >
> >
> > use [AdventureWorks]
> >
> > GO
> >
> > GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> >
> > GO
> >
> >
> >
> > /*Now, open up a "file/new/DB Engine Query" and login with the test
> > login*/
> >
> >
> >
> > exec getDecryptedIDNumber
> >
> >
> >
> > /*This returns NULL values where it should show the decrypted data*/
> >
> >
> >
> > Can someone please assist?
> >
> >
> >
> > TIA, ChrisR
> >
> >
> >
>

Decrypt permissions?

Howdy all. I just did the BOL example of encrypting a column of data:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
6cda0adc.htm
and it worked great. However, Ive been trying to figure out how to let an
end user (someone with just read permissions) decrypt the data for a while
now to no avail. Can someone please assist? Also, I had to use "
WITH ALGORITHM = DES"
instead of what BOL said to to my version of SQL Server.
TIA, ChrisR
ChrisR wrote:
> Howdy all. I just did the BOL example of encrypting a column of data:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
> 6cda0adc.htm
> and it worked great. However, Ive been trying to figure out how to let an
> end user (someone with just read permissions) decrypt the data for a while
> now to no avail. Can someone please assist? Also, I had to use "
> WITH ALGORITHM = DES"
> instead of what BOL said to to my version of SQL Server.
> TIA, ChrisR
The end user can access the data through a stored proc that decrypts
the data for him. Stored procs are the preferred method for data access
whether or not the data is encrypted. Execute permission is all that
the user will need.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thank you David. Do you happen to know if it can be done without an SP or
not? We replicate a lot of data to a "reporting box", with the sole purpose
in mind of developers and power users being able to query their own data.
Thanks again.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1164063291.160874.187260@.b28g2000cwb.googlegr oups.com...[vbcol=seagreen]
> ChrisR wrote:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7[vbcol=seagreen]
an[vbcol=seagreen]
while
> The end user can access the data through a stored proc that decrypts
> the data for him. Stored procs are the preferred method for data access
> whether or not the data is encrypted. Execute permission is all that
> the user will need.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||The whole ide of using an SP is to provide access to the data without
granting permissions to the base table. Can you avoid using an SP? Sure but
that means you will end up providing access to the encryption key and table
which is usually not a good thing to do if you are really concerned about
security.
You could tweak the SP to have parameter for the calling userID (assuming
you have some way to determine the rows for "permitted" users) and filter
the results with a where clause but that can be a sizeable perf hit if
you're dealing with large tables and/or many concurrent users. If they don't
mind since it's a reporting server, lucky you.
joe.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OVIC2SRDHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Thank you David. Do you happen to know if it can be done without an SP or
> not? We replicate a lot of data to a "reporting box", with the sole
> purpose
> in mind of developers and power users being able to query their own data.
> Thanks again.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1164063291.160874.187260@.b28g2000cwb.googlegr oups.com...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
> an
> while
>
|||Thanks Joe. I understand the importance of security, but (unfortunately) the
powers that be want to be able to directly query their data. You brought up
a good point though that I hadn't thought of, passing out the encryption key
is a strong arguement, and won I can probably win.
Thanks!
"Joe Yong" <NO_jyong@.SPAM_scalabilityexperts.com> wrote in message
news:excTblRDHHA.4404@.TK2MSFTNGP03.phx.gbl...
> The whole ide of using an SP is to provide access to the data without
> granting permissions to the base table. Can you avoid using an SP? Sure
but
> that means you will end up providing access to the encryption key and
table
> which is usually not a good thing to do if you are really concerned about
> security.
> You could tweak the SP to have parameter for the calling userID (assuming
> you have some way to determine the rows for "permitted" users) and filter
> the results with a where clause but that can be a sizeable perf hit if
> you're dealing with large tables and/or many concurrent users. If they
don't[vbcol=seagreen]
> mind since it's a reporting server, lucky you.
>
> joe.
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:OVIC2SRDHHA.4832@.TK2MSFTNGP06.phx.gbl...
or[vbcol=seagreen]
data.[vbcol=seagreen]
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7[vbcol=seagreen]
let
>
|||Windows 200 Pro SP4.
SQL 2005 SP1.
I can get the decryption to work as me, but if I open up a new connection
with my test user, it returns NULL values instead of the decrypted data.
This is even if I create a proc and grant exec rights to the test user. Here
is precisely what I did:
USE AdventureWorks;
GO
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Can someone please assist?
TIA, ChrisR
|||You're missing the "EXECUTE AS OWNER" or execute as something-or-other
clause in your create procedure statement. If you don't have that clause, it
will default to execute as caller which means any user executing that sproc
will be checked for permissions and if it isn't granted, you get null values
in your SELECT.
joe.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:ONry7jYDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> Windows 200 Pro SP4.
> SQL 2005 SP1.
> I can get the decryption to work as me, but if I open up a new connection
> with my test user, it returns NULL values instead of the decrypted data.
> This is even if I create a proc and grant exec rights to the test user.
> Here
> is precisely what I did:
>
> USE AdventureWorks;
> GO
>
> --If there is no master key, create one now
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
> r$$#1946kcj$n44ncjhdlj'
> GO
>
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
>
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
>
> USE [AdventureWorks];
> GO
>
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
>
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
>
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
>
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
>
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
>
> /*works for me*/
> exec getDecryptedIDNumber
>
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
>
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
>
> /*Now, open up a "file/new/DB Engine Query" and login with the test
> login*/
>
> exec getDecryptedIDNumber
>
> /*This returns NULL values where it should show the decrypted data*/
>
> Can someone please assist?
>
> TIA, ChrisR
>
>
|||Thanks Joe, but I just tried:
ALTER procedure [dbo].[getDecryptedIDNumber]
with execute as 'dbo'
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
use [AdventureWorks]
GO
GRANT IMPERSONATE ON USER::[dbo] TO [test]
GO
exec as user = 'dbo'
exec getDecryptedIDNumber
and it still comes back as NULL. Any other ideas?
"Joe Yong" <NO_jyong@.SPAM_scalabilityexperts.com> wrote in message
news:ONp1UqeDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> You're missing the "EXECUTE AS OWNER" or execute as something-or-other
> clause in your create procedure statement. If you don't have that clause,
it
> will default to execute as caller which means any user executing that
sproc
> will be checked for permissions and if it isn't granted, you get null
values[vbcol=seagreen]
> in your SELECT.
>
> joe.
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:ONry7jYDHHA.3524@.TK2MSFTNGP06.phx.gbl...
connection[vbcol=seagreen]
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117[vbcol=seagreen]
CHECK_POLICY=OFF
>