Problem with Transparent Data Encryption

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Problem with Transparent Data Encryption

Author

Topic

neeraj27
Starting Member

5 Posts

Posted - 2010-04-18 : 00:10:07

Hello Everyone,

I want to encrypt database using TDE, as per MSDN & other resources i've run following queries

use master

go

create master key
encryption by password=N'csce548';
go

create certificate tde_csce548
with subject=N'TDE encryption server certificate';

go

backup certificate tde_csce548
to file=N'D:\neeraj books\CSCE 548 Building secure software\project\mserver_certificate.cer'
with private key
(
file = N'D:\neeraj books\CSCE 548 Building secure software\project\mserver_certificate.pvk',
encryption by password=N'csce548'
);

go

Things go fine till here but when I try to encrypt database BSS by giving following queries

use BSS

go

create database encryption key
with algorithm=AES_128
encryption by server certificate tde_csce548;

go

it gives error:Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'KEY'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I really need some urgent help, plz. help me out.

email: neerajagrawal27.in@gmail.com

Kristen
Test

22859 Posts

Posted - 2010-04-18 : 02:41:06

What is the compatibility mode of BSS database?


SELECT	cmptlevel, [name]
FROM	sys.sysdatabases
ORDER BY [name]

neeraj27
Starting Member

5 Posts

Posted - 2010-04-18 : 12:33:17

Hi Kristen
Thanks for Replying

Its 90 for all of them.....

Kristen
Test

22859 Posts

Posted - 2010-04-18 : 14:08:29

I think Transparent Data Encryption is SQL 2008 (thus needs Compatibility Mode = 100) ??

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-18 : 14:26:30

The syntax should still be valid under mode 90. I tested on a DB in compat mode 90 on my server, and it ran fine.

You said *all* your databases have mode 90. Including the system databases? Can you run the following and post the results?

SELECT @@version

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-18 : 14:27:26

quote:
Originally posted by Kristen

SELECT cmptlevel, [name]
FROM sys.sysdatabases
ORDER BY [name]

btw, sysdatabases deprecated, included for backward compat, use sys.databases for new development.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-04-18 : 15:50:07

Sorry, typo :(

I couldn't find any other way to view Compatibility Mode, is there a PROPERTIES function or somesuch that lists it?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-18 : 16:02:06

[code]select name, compatibility_level from sys.databases[/code]

It's not in the DatabaseProperty function, probably cause there's no need, it's straight visible in the system view.

--
Gail Shaw
SQL Server MVP

neeraj27
Starting Member

5 Posts

Posted - 2010-04-18 : 16:03:49

quote:
Originally posted by GilaMonster

The syntax should still be valid under mode 90. I tested on a DB in compat mode 90 on my server, and it ran fine.

You said *all* your databases have mode 90. Including the system databases? Can you run the following and post the results?
SELECT @@version

--
Gail Shaw
SQL Server MVP

Hi Gail

Ya all my databases have mode 90.

also I am using SQL server 2008 Enterprise Edition.

but on executing select @@version its showing

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: )

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-18 : 16:24:03

No, you don't have SQL 2008 Enterprise Edition. You have SQL 2005 Express edition, as the @@version confirms.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: )

You may be using 2008 Management studio, but the server that you're connected to is SQL 2005. Since TDE is a 2008 only feature (and Enterprise only), it's not a surprise it's not working. Perhaps you've connected to the wrong server?

--
Gail Shaw
SQL Server MVP

neeraj27
Starting Member

5 Posts

Posted - 2010-04-18 : 22:30:40

quote:
Originally posted by GilaMonster

No, you don't have SQL 2008 Enterprise Edition. You have SQL 2005 Express edition, as the @@version confirms.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: )

You may be using 2008 Management studio, but the server that you're connected to is SQL 2005. Since TDE is a 2008 only feature (and Enterprise only), it's not a surprise it's not working. Perhaps you've connected to the wrong server?

--
Gail Shaw
SQL Server MVP

Thanks Gail

I have reinstalled it but its still unable to connect to 2008 server. I have enterprise version 2008. Please help me out.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-19 : 03:12:04

You need the server name of the 2008 server, and appropriate credentials. I can't give you those. Make sure you're entering the correct servername when you connect via management studio.

Are you sure that you have enterprise edition? Are you sure you're installing the correct edition? Assuming this is a test/personal machine, not a production server, where did you get enterprise edition from (and what about the licence fees)?

--
Gail Shaw
SQL Server MVP

neeraj27
Starting Member

5 Posts

Posted - 2010-04-19 : 10:41:32

quote:
Originally posted by GilaMonster

You need the server name of the 2008 server, and appropriate credentials. I can't give you those. Make sure you're entering the correct servername when you connect via management studio.

Are you sure that you have enterprise edition? Are you sure you're installing the correct edition? Assuming this is a test/personal machine, not a production server, where did you get enterprise edition from (and what about the licence fees)?

--
Gail Shaw
SQL Server MVP

Ya I am giving correct server name, the one which I created at time of installation but its not connecting. May be I have messed up something somewhere, and ya I do understand that its not possible to help me with this problem here. I appreciate your help and time.

About license, I am a student at Univ. of South Carolina & my university has some sort of tie up with Microsoft & thus provides us lots of Microsoft software for free.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-19 : 11:41:49

If you're getting a login failure, check the error log for the reason. If it's not finding the server, check the name, the instance name (if applicable), that the service is running.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources