Transparent Data Encryption (TDE) SQL Server 2008

Question

Post reply

Transparent Data Encryption (TDE) SQL Server 2008

Viewing 15 posts - 61 through 75 (of 81 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Thank Eric for the compliments.. And Thanks Steve for clearing Erics doubt.

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 2

Hello Roy,

I successfully implemented TDE on SQL Server 2008 without SP1 before.

Now I am testing the TDE on SQL Server 2008 with SP1 (version 10.0.2531.0) but it is not working any more (encryption_state is equal 2 forever).

The files are not ready only and there is no filestream data type used.

Do you know what the problem is?

Thanks a lot.

Eric

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Hi Eric,

Did you check the percent_complete value from the sys.dm_database_encryption_keys? Also are you trying to set up TDE on the same system where you tested without SP1?

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 4

Hi Roy,

The percent_complete = 0 and never changed. It was working on a different server (VM) without SP1 before. The server was destroyed after the testing.

Now I am working on a new server with SQL server 2008 + SP1 on Windows Server 2008.

Thanks,

Eric

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Under what user is the service running? This looks more like permission issue. That is probably why encryption_state is 2.

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 6

Under a domain user account. This user has Administrator's rights on the Windows server and is assigned sysadmin role on SQL server.

Thanks,

Eric

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

Is there anything in the SQL error log? Till now no one has reported issues in setting up TDE on SP1 and Windows 2008.

Keep in mind that Windows 2008 OS has this new security feature that it will not allow some file sttirbutes modification if you do not run the application as administrator. What I would like you to try is when you are opening SSMS, right click on the shortcut and choose "Run as administrator"

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 8

Did as what you said but the percent_complete is still shown 0.

I also think it might be a permission issue but no idea what it is.

Thanks a lot,

Eric

Patrick-386510 SSC Rookie Points: 33 More actions · Answer 9

I have the same problem, the status is 2 but in my case percent complete is 100

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Really excellent article - nice and clear, easy to understand, and seems to cover all the issues. Thanks Roy.

Tom

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

Thanks Tom. I tried to do that and I guess I succeeded.

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 12

Hello, Roy,

The TDE works fine on SQL Server 2008 R2 for me.

Now I am trying to use SQL Audit in SQL Server 2008.

Everything is working but the Audit log date somehow is different from the SQL Sever or Windows System date. For example, the current date is 11/16/2010 3:48 PM but the Audit log date displays as 11/16/2010 8:48 PM.

Do you have any idea?

Thanks a lot for your help in advance.

Eric

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 13

Do you by any chance have any time zone difference? TDE should not have any issues with Auditing. But I have heard that Auditing can have issues with replication.

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 14

Thanks Roy.

We are in Eastern Time zone.

I think the time zone is not a problem.

The problem is that the SQL Audit event log time is different from the Server current date.

It is applying to SQL Audit alone. Nothing to do with TDE.

Thanks,

Eric

TomThomson SSC Guru Points: 104773 More actions · Answer 15

Eric Min (11/17/2010)
Thanks Roy.
We are in Eastern Time zone.
I think the time zone is not a problem.
The problem is that the SQL Audit event log time is different from the Server current date.
It is applying to SQL Audit alone. Nothing to do with TDE.
Thanks,
Eric

Eastern Time Zone? As in UTC-5? And 3:48 server time is 8:48 SQL Audit Log time according to your earlier post. That looks as if the SQL audit event log time is UTC time while the Server current date is Easter Time.

I used to get awfully bored explaining to people that the default IIS log format used UTC even in India, and even in the summer in the UK. Don't know if SQL Server Audit logging does the same.

Tom