Primary/Foreign/Identity keys & Encryption

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

Hey Ben,

You have the principle behind encryption correct.

The tricky thing is when you use encryption on keys, a lot of the value of having these keys goes away. For eample, because encryption is non-deterministic, you won't be able to use OrderID as a FK in the OrderDetails table (unless if you encrypt once and then insert into both tables, but this leaks information. Then again, this might be acceptable in your application). You can still use PK, but they will behave differently. For example, because encryption is non-deterministic, just having the primary keys no longer guarantees that the columns will be unique. If you try to insert Encrypted("id1") and then Encrypted("id1") again, you actually end up with two different cipher values so the the table will allow both inserts.

Security basically destroys information (well encrypted data is indistinguishable from random data) while the point of using keys is to preserve information for reference. Consider, for example, the difficulties you will encounter attempting to do searches or joins on CustomerID and OrderID if they are encrypted (Laurentiu has a good blog entry on this here: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx).

You can also check Laurentiu's blog for an example of creating an application using encryption: http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx this doesn't completely solve your problems, but it might be useful in seeing one way to apply encryption.

Please let us know if you would like more information or have further questions.

Sung

sql

Primary key and identity fields information got lost by "Import Da

Hi all!
I want to import data from Sql Server 2000 into Sql Server 2005.
When i select "Import Data" in 2005 and import tables directly from the 2000
Data Source, the primary keys and the identity fields information is lost in
2005.
Who knows why?
Best regards
Ulrich Schumacher
urlich
How about BACKUP /RESTORE commands
Or you can create a linked server to SQL Server 2000 and run SELECT * INTO
Schema.Mytable FROM SQL2K.Database.DBO.Table it ON SQL Server 2005
"ulrich schumacher" <ulrichschumacher@.discussions.microsoft.com> wrote in
message news:D20F7B7B-5C85-441B-AFE5-14191B3DF6CA@.microsoft.com...
> Hi all!
> I want to import data from Sql Server 2000 into Sql Server 2005.
> When i select "Import Data" in 2005 and import tables directly from the
> 2000
> Data Source, the primary keys and the identity fields information is lost
> in
> 2005.
> Who knows why?
> Best regards
> Ulrich Schumacher
>

Primary key and identity fields information got lost by "Import Da

Hi all!
I want to import data from Sql Server 2000 into Sql Server 2005.
When i select "Import Data" in 2005 and import tables directly from the 2000
Data Source, the primary keys and the identity fields information is lost in
2005.
Who knows why?
Best regards
Ulrich Schumacherurlich
How about BACKUP /RESTORE commands
Or you can create a linked server to SQL Server 2000 and run SELECT * INTO
Schema.Mytable FROM SQL2K.Database.DBO.Table it ON SQL Server 2005
"ulrich schumacher" <ulrichschumacher@.discussions.microsoft.com> wrote in
message news:D20F7B7B-5C85-441B-AFE5-14191B3DF6CA@.microsoft.com...
> Hi all!
> I want to import data from Sql Server 2000 into Sql Server 2005.
> When i select "Import Data" in 2005 and import tables directly from the
> 2000
> Data Source, the primary keys and the identity fields information is lost
> in
> 2005.
> Who knows why?
> Best regards
> Ulrich Schumacher
>

primary file group run out of free space??

Hi all
I got the following error massage when tried to delete some 3 GB records
from a 26 GB records table - 'tblName':
'Could not allocate space for object 'tblName' in database 'dbName' because
the primary
filegroup is full'.
At the time of this error there were some 4 GB free disk space on the disk
where the datababse data and log file were.
Also these files were not limitted in grow size.
My questions are:
1) why does a 'delete' operation involves allocation of space for the table
that is being deleted'
Does this operation involves creating image data for the table that is being
deleted, in the transaction log, where the deletion actually occures and
than commited back to the original table'
So if i am deleting data from a 26GB table, there should be this amount of
free space on the disk where the log's file group is located?
2) Where can i see the state of the primary filegroup and how can i increase
its size?
Thanks for your attention
ReaRea
delete is a logged operation , so have you seen the log file during the
deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Perfrom your DELETION (TRUNCATION would be more efficient)
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I got the following error massage when tried to delete some 3 GB records
> from a 26 GB records table - 'tblName':
> 'Could not allocate space for object 'tblName' in database 'dbName'
because
> the primary
> filegroup is full'.
> At the time of this error there were some 4 GB free disk space on the disk
> where the datababse data and log file were.
> Also these files were not limitted in grow size.
> My questions are:
> 1) why does a 'delete' operation involves allocation of space for the
table
> that is being deleted'
> Does this operation involves creating image data for the table that is
being
> deleted, in the transaction log, where the deletion actually occures and
> than commited back to the original table'
> So if i am deleting data from a 26GB table, there should be this amount of
> free space on the disk where the log's file group is located?
> 2) Where can i see the state of the primary filegroup and how can i
increase
> its size?
>
> Thanks for your attention
> Rea
>|||Thanks alot!
So how much disk space should a deletion of 3 GB from a 26GB table consume'
Is there a way to estimate the amount of disk space deletion operations
consume from
the transaction log's disk'
2) in your code below: what is the edvantage of doing deletions this way'
Thanks again
Rea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Rea
> delete is a logged operation , so have you seen the log file during the
> deletion?
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> --Perfrom your DELETION (TRUNCATION would be more efficient)
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> because
disk[vbcol=seagreen]
> table
> being
of[vbcol=seagreen]
> increase
>|||Rea
I divide a long/big transaction into a small one.
CHECKPOINT flows a data from transaction log into the disk to remove an
inactive portions (btw you can also perform BACKUP LOG operation)
With that way you don't lock others by running your big deletion and also
keep a log file with an appropriate size.
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:OPOU01AZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks alot!
> So how much disk space should a deletion of 3 GB from a 26GB table
consume'
> Is there a way to estimate the amount of disk space deletion operations
> consume from
> the transaction log's disk'
> 2) in your code below: what is the edvantage of doing deletions this way'
> Thanks again
> Rea
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
records[vbcol=seagreen]
> disk
and[vbcol=seagreen]
amount[vbcol=seagreen]
> of
>

Previous YTD

Hi all

I am trying to perform Pervious Year To Date Total (which is like YTD but for the previous year. ie. ytd would work for this year's jan -> whatever month. But I need a total from Last year from jan -> whatever year to do comparision), However I've ran into the following issues.

Since Year is a variable and it can change depending on which year you select. I tried to Return the first member of the month which in theory should be January of a Year. However when I performed the below query I got Dec which was the overall period through out the entire Month this include 2002 dec -> 2005 May period so the head returned Dec.

SELECT { head(Descendants(
[Period].CurrentMember,
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )
So then I tried to return the Head of the Set of the current year using the below query. But no matter what I do I can not get it working.

SELECT { head(Descendants(
{[Period].CurrentMember, [Period].[Month]},
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )

How would you guys usually do a previous year to date comparision ? since year is a variable

Thanks
Tom

This isn't clear. Can you be more explicit?|||hi vector

I think you've answered my question on the other thread. I will test this when I get into office first thing in the morning.

thanks alot !!

Preventing windows users accessing a database

Hi All
I want to prevent windows users from accessing my database on SQL server
express 2005
I don't want users to be able to login with SSME on Windows authentication,
only by SQl Server Authentication and only on the sa and another specific
login with the password I have set
For the life of me I can't seem to find how to do this
Can anybody advise me
Regards
SteveSteve (ga630sf@.newsgroups.nospam) writes:
> I want to prevent windows users from accessing my database on SQL server
> express 2005
> I don't want users to be able to login with SSME on Windows
> authentication, only by SQl Server Authentication and only on the sa and
> another specific login with the password I have set
> For the life of me I can't seem to find how to do this
First of all, if you granted access to any Windows login or groups,
remove these. Second, also revoke access to BUILTIN\Administrators,
which gives permission to all Windows logins that have admin rights
on the machine.
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.mspx|||Steve
I think the following has been introduced in SP2 and I did not test on
Express Edition ,sorry
/*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
Issue a rollback*/
for logon
as begin
if exists (select * from sys.server_principals
where type_desc ='Windows_Login'
and name=original_login() )
begin
ROLLBACK;
end
end
go
For more details please
"Steve" <ga630sf@.newsgroups.nospam> wrote in message
news:edw2AkkzHHA.4004@.TK2MSFTNGP05.phx.gbl...
> Hi All
> I want to prevent windows users from accessing my database on SQL server
> express 2005
> I don't want users to be able to login with SSME on Windows
> authentication, only by SQl Server Authentication and only on the sa and
> another specific login with the password I have set
> For the life of me I can't seem to find how to do this
> Can anybody advise me
>
> Regards
> Steve
>|||Erland
Thanks worked a treat
Regards
steve
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99786FED46931Yazorman@.127.0.0.1...
> Steve (ga630sf@.newsgroups.nospam) writes:
> First of all, if you granted access to any Windows login or groups,
> remove these. Second, also revoke access to BUILTIN\Administrators,
> which gives permission to all Windows logins that have admin rights
> on the machine.
>
> --
> 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.mspx