Endpoint Security ?

Hi There

I am trying to grasp endpoint security, or actually more security/certiicates in general, at the moment i am trying to write a distributed service broker app, all the examples i have seen use certificates for the endpoint authentication.

Why must one create a certificate at each endpoint? Why can i not create a single certificate and let all endpoints use it ?

As you can imagine of this app gets distributed to hundreds of places creating a certificate at each one is a mission?

So can i use a single certificate for all endpoints authentication?

Thanx

The certificates based authentication (in general, not only for Service Broker) relies on the fact that the private key is a well guarded secret. Being a secret, proof of possesion of the private key (e.g. a cryptographic signature) can be used as proof of identity. The moment you're talking about multiple copies of the same private key, it's value as a identity proof is greatly diminished. It is impossible in practice to ensure the secrecy of the private key while is deployed at hundreds of sites.

What you can consider is to allow public connectivity to your server. That is, grant CONNECT to [Public] on the broker endpoint and grant SEND to [Public] on the target service. This allows anybody to connect ot the endpoint and anybody to send a message to the service (using anonymous dialog security).

If public connectivity is not acceptable, then you must create hundreds odf certificates and manage them. Using one certificate instead and copying the private key hundreds of time doesn't give you any real security: since your private key is very likely to be leaked outside your control, all you have is just a false sense of security.

HTH,
~ Remus

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

sql

Encryption of Credit Card files

G/Day Forum,
We are working on complying with the Visa/MAsterCard Payment Card Industry
Data Security Standard (PCI DSS). As part of this we need to imply the
following controls on the storage of credit card data:
to encrypt data at a folder level - that is all of the containing folders
and files
to allow for split knowledge of encryption keys and management thereof
to allow for strong encryption support (algorithms like 3DES, AES, etc)
a mechanism for automating the encryption process on a daily basis - this is
coincide with a backup cycle (no clear text credit card files get backed up
onto tape)
We are looking for a File/Folder encryption solution for a Windows 2000
based file server (member of a Windows 2000 Domain) and a Windows 2003 based
FTP Server (Standalone system), that will be used for storing Credit Card
information.
Your thoughts on any products that suit my requirements?
Regards,
Steve."The Poster" <nospam@.nospam_dontyoudare.net> wrote in message
news:eYqW%23rpGGHA.1396@.TK2MSFTNGP11.phx.gbl...
> G/Day Forum,
> We are working on complying with the Visa/MAsterCard Payment Card Industry
> Data Security Standard (PCI DSS). As part of this we need to imply the
> following controls on the storage of credit card data:
> to encrypt data at a folder level - that is all of the containing folders
> and files
> to allow for split knowledge of encryption keys and management thereof
I don't follow. Do you mean so that no single person can decrypt the credit
card information alone? Is that part of the PCI DSS requirements?

> to allow for strong encryption support (algorithms like 3DES, AES, etc)
> a mechanism for automating the encryption process on a daily basis - this
is
> coincide with a backup cycle (no clear text credit card files get backed
up
> onto tape)
Why would you not always encrypt the credit card numbers immediately instead
of on a schedule? I would think this would be highly preferable.

> We are looking for a File/Folder encryption solution for a Windows 2000
> based file server (member of a Windows 2000 Domain) and a Windows 2003
based
> FTP Server (Standalone system), that will be used for storing Credit Card
> information.
Windows EFS will do this. Make sure however that you 1) configure EFS
securely according to best practices and 2) you MUST back up your encryption
keys. You can meet the requirement if necessary of no single person being
able to decrypt by encrypting using an account where two people each know
half of the password. If you want other combinations of people to be able
to decrypt the data, you could encrypt the data in different ways using
different accounts where different people share the password. PGP, GPG and
www.jetico.com are some other popular low-cost encryption programs that work
similarly and may or may not meet your needs.
Or you could have a developer program a custom solution that handles the
data encryption / decryption and has a front-end that manages user
authentication to see the decrypted data.|||You plan on storing this in files ? as opposed to in database ? and,
more surprising to me, on a machine that has FTP active ' !?
Your interpretation of the guidance does not sound right when you say
> a mechanism for automating the encryption process on a daily basis - this
> is
> coincide with a backup cycle (no clear text credit card files get backed
> up
> onto tape)
I thought it says never stored (anywhere) in the clear
"The Poster" <nospam@.nospam_dontyoudare.net> wrote in message
news:eYqW%23rpGGHA.1396@.TK2MSFTNGP11.phx.gbl...
> G/Day Forum,
> We are working on complying with the Visa/MAsterCard Payment Card Industry
> Data Security Standard (PCI DSS). As part of this we need to imply the
> following controls on the storage of credit card data:
> to encrypt data at a folder level - that is all of the containing folders
> and files
> to allow for split knowledge of encryption keys and management thereof
> to allow for strong encryption support (algorithms like 3DES, AES, etc)
> a mechanism for automating the encryption process on a daily basis - this
> is
> coincide with a backup cycle (no clear text credit card files get backed
> up
> onto tape)
> We are looking for a File/Folder encryption solution for a Windows 2000
> based file server (member of a Windows 2000 Domain) and a Windows 2003
> based
> FTP Server (Standalone system), that will be used for storing Credit Card
> information.
> Your thoughts on any products that suit my requirements?
> Regards,
> Steve.
>
>|||I'll echo Roger's comments. Aside from asking for product advice, it maybe
worthwhile to review your architecture/goals.
Storing credit card information implies that it will be retrieved for future
use. Aside from normal retail operations like allowing customers to "save"
payment information for a quicker checkout process on a subsequent sale,
either by themselves online, or via telephone with a rep, the only other
probable use is for some data mining - but I don't think you need the entire
number to run reports based on credit cards.
On a large scale, say you have multiple "local" locations that run their own
localized sales/ops and then "batch" data into a central location (my guess
for your FTP need), the question still remains, what is the purpose for
including credit card information in such a batching process? I'll assume
this is just to allow the scenario I mentioned - allowing customers
easier/faster experience on a subsequent sale, they may have bought an item
from Store A in CA, but can still have the same ease if they ordered through
your web site or call center in NY or anywhere. In this case, the question
which Roger already asked is, why FTP instead of a synchronized database? If
you are at this scale of operations, then it would only be fitting to have
the proper architecture for it.
Cheers,
Ed
"The Poster" wrote:

> G/Day Forum,
> We are working on complying with the Visa/MAsterCard Payment Card Industry
> Data Security Standard (PCI DSS). As part of this we need to imply the
> following controls on the storage of credit card data:
> to encrypt data at a folder level - that is all of the containing folders
> and files
> to allow for split knowledge of encryption keys and management thereof
> to allow for strong encryption support (algorithms like 3DES, AES, etc)
> a mechanism for automating the encryption process on a daily basis - this
is
> coincide with a backup cycle (no clear text credit card files get backed u
p
> onto tape)
> We are looking for a File/Folder encryption solution for a Windows 2000
> based file server (member of a Windows 2000 Domain) and a Windows 2003 bas
ed
> FTP Server (Standalone system), that will be used for storing Credit Card
> information.
> Your thoughts on any products that suit my requirements?
> Regards,
> Steve.
>
>

Encryption & Decryption

Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.

We got the code working for the seond case but for the first, WHEN:

CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM =
AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_Key
GO

THEN:

-- Msg 15314, Level 16, State 1, Line 1
-- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

What can be the way out to be able to create the AES 256 Symmetric key.

AES is currently supported by SQL Server 2005 only on Windows 2003. If you are using Windows 2000 or Windows XP, you do not have access to AES encryption from SQL Server.

Thanks
Laurentiu

sql

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response Rick.
"Rick Sawtell" wrote:
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> > Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> > files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
>> files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
>> files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response Rick.
"Rick Sawtell" wrote:

> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the dat
a
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks for the response Rick.
"Rick Sawtell" wrote:

> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypt and ?Decrypt Data?

I Also Post this to SQL Server Security section no one aswered.

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

Here is the post:

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

Do you have permissions on that key ? otherwise it will return false.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||How can i know it. And is it possible not to be owner of the key which i created. And it returns NULL value like i say that. What do u mean with false?|||

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

Answered. Thanks

Encrypt and ?Decrypt Data?

I Also Post this to SQL Server Security section no one aswered.

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

Here is the post:

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

Do you have permissions on that key ? otherwise it will return false.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||How can i know it. And is it possible not to be owner of the key which i created. And it returns NULL value like i say that. What do u mean with false?|||

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

Answered. Thanks

enable security auditing ..how ?

Came across this on the checklist for best practises
Enable security auditing
. Enable security auditing of Sysadmin actions, fixed role membership
changes, all login related activity, and password changes.
. After selecting appropriate auditing options, you should
script the audit, wrap it in a stored procedure,and mark that stored
procedure for AutoStart.
But dont really know how to go about it ? Is this part of Profiler ? And if
so, will it be an overkill to run on a continuous basis on a server that may
be highly transactional ?Which checklist and which version of SQL Server?
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.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7RFiNKIGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Came across this on the checklist for best practises
> Enable security auditing
> . Enable security auditing of Sysadmin actions, fixed role membership
> changes, all login related activity, and password changes.
> . After selecting appropriate auditing options, you should
> script the audit, wrap it in a stored procedure,and mark that stored
> procedure for AutoStart.
>
>
> But dont really know how to go about it ? Is this part of Profiler ? And
> if so, will it be an overkill to run on a continuous basis on a server
> that may be highly transactional ?
>

Enable Report Builder for User

I cannot figure out how to display the 'Report Builder' icon in the toolbar for a user.

I've tried setting the different security roles, but the icon doesn't display.

Thanks.

I guess what I should have asked was -- "How do I deploy Report Builder to a user?"

I've found that this URL will execute Report Builder -- http://server name/reportserver/reportbuilder/reportbuilder.application

What is the best way to deploy ReportBuilder to a user?

|||

Hi,

If I am correct, you want the user to build his own ad-hoc reports using the report model. If this is correct, when the user click on the report builder, he will be able to select the report models (already deployed on the production server).

When user selects the report model and the format for the report, the report builder is available to the user as clickonce application. It would require .NET framework to be available on the user's machine as prerequiste.

Hope this helps.

Virendra

|||

Hi Virendra,

I am having the same issue with Report Builder as Pwomack.

Like Pwomack i found that same link to http://<<server>>/reportserver/reportbuilder/reportbuilder.application although i couldn't use it because our users work with Res Powerfuse in a Terminal Server environment (users don't have access to the local C: drive and .Net Framework tries to place Tmp files in Documents & Settings\username\local settings\... which is prohibited in our environment / infrastructure.)

I've been testing and i've tried to adjust user rights and such but without luck. The .Net framework is installed on our servers. Do you have any other suggestions where i could look?

With regards,

Gawelles

Enable Report Builder for User

I cannot figure out how to display the 'Report Builder' icon in the toolbar for a user.

I've tried setting the different security roles, but the icon doesn't display.

Thanks.

I guess what I should have asked was -- "How do I deploy Report Builder to a user?"

I've found that this URL will execute Report Builder -- http://server name/reportserver/reportbuilder/reportbuilder.application

What is the best way to deploy ReportBuilder to a user?

|||

Hi,

If I am correct, you want the user to build his own ad-hoc reports using the report model. If this is correct, when the user click on the report builder, he will be able to select the report models (already deployed on the production server).

When user selects the report model and the format for the report, the report builder is available to the user as clickonce application. It would require .NET framework to be available on the user's machine as prerequiste.

Hope this helps.

Virendra

|||

Hi Virendra,

I am having the same issue with Report Builder as Pwomack.

Like Pwomack i found that same link to http://<<server>>/reportserver/reportbuilder/reportbuilder.application although i couldn't use it because our users work with Res Powerfuse in a Terminal Server environment (users don't have access to the local C: drive and .Net Framework tries to place Tmp files in Documents & Settings\username\local settings\... which is prohibited in our environment / infrastructure.)

I've been testing and i've tried to adjust user rights and such but without luck. The .Net framework is installed on our servers. Do you have any other suggestions where i could look?

With regards,

Gawelles

Enable Common Criteria Compliance

SQL 2005, SP2
In the Security section of the Server Properties dialog in SSMS, there is a
check option for "Enable Common Criteria Compliance". In the online help,
this position is listed as the "Allow Direct Updates to System Tables"
option that has been around for a while.
Does anyone know if the "Enable Common Criteria Compliance" is a one for one
replacement for the "Allow Direct Updates to System Tables" or does if have
different functionality? I did google and Microsoft web searches and
couldn't come up with any hits on the Enable Common Criteria option.
ThanksSorry for duplicate messages. I had an operator malfunction.
"Al" <zyck@.tconl.com> wrote in message
news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is
> a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for
> one
> replacement for the "Allow Direct Updates to System Tables" or does if
> have
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>|||Did you also update your Books Online (separate download)? I did, and search
ing for below gave me
three hits. It is *not* the same as the old "allow update", they probably on
ly re-used the BOL URL
(or similar).
Enable Common Criteria Compliance
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Al" <zyck@.tconl.com> wrote in message news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...agreen">
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is
a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for o
ne
> replacement for the "Allow Direct Updates to System Tables" or does if hav
e
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>

Enable Common Criteria Compliance

SQL 2005, SP2
In the Security section of the Server Properties dialog in SSMS, there is a
check option for "Enable Common Criteria Compliance". In the online help,
this position is listed as the "Allow Direct Updates to System Tables"
option that has been around for a while.
Does anyone know if the "Enable Common Criteria Compliance" is a one for one
replacement for the "Allow Direct Updates to System Tables" or does if have
different functionality? I did google and Microsoft web searches and
couldn't come up with any hits on the Enable Common Criteria option.
Thanks
Sorry for duplicate messages. I had an operator malfunction.
"Al" <zyck@.tconl.com> wrote in message
news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is
> a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for
> one
> replacement for the "Allow Direct Updates to System Tables" or does if
> have
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>

Enable Common Criteria Compliance

SQL 2005, SP2
In the Security section of the Server Properties dialog in SSMS, there is a
check option for "Enable Common Criteria Compliance". In the online help,
this position is listed as the "Allow Direct Updates to System Tables"
option that has been around for a while.
Does anyone know if the "Enable Common Criteria Compliance" is a one for one
replacement for the "Allow Direct Updates to System Tables" or does if have
different functionality? I did google and Microsoft web searches and
couldn't come up with any hits on the Enable Common Criteria option.
ThanksSorry for duplicate messages. I had an operator malfunction.
"Al" <zyck@.tconl.com> wrote in message
news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is
> a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for
> one
> replacement for the "Allow Direct Updates to System Tables" or does if
> have
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>|||Did you also update your Books Online (separate download)? I did, and searching for below gave me
three hits. It is *not* the same as the old "allow update", they probably only re-used the BOL URL
(or similar).
Enable Common Criteria Compliance
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Al" <zyck@.tconl.com> wrote in message news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for one
> replacement for the "Allow Direct Updates to System Tables" or does if have
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>