Hi
There is a very sensitive table that holds a very sensitive field (i.e. a person's salary). I have a requirement to programmatically encrypt it when the value is stored (I can do that quite easily in the VB client app), but there are times when a suitably 'sa' user should be allowed to perform a SELECT of the table and to view the salary field in the clear.
I think a SQL2000 user-defined function can do the job. The catch is that the client is running SQL 7 and he will only upgrade 'over his dead body', or words to that effect.
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK) WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
I understand that Dialog Security + certificates can be used to encryption individual dialogs. I have several demos working now that do just this.However, I don't fully understand exactly when the messages are encrypted, and if they are ever written to a queue on the initiating service prior to being encrypted. I want to make sure that admins can't simply query the transmission queues to get clear text messages, because I have strict requirements that I encrypt all personal data that is stored anywhere in a database. BOL is a little unclear on this topic. The relevant doc reads:For a dialog that uses security, Service Broker encrypts all messages sent outside a SQL Server instance. Messages that remain within a SQL Server instance are never encrypted. In dialog security, only the database that hosts the initiating service and the database that hosts the target service need to have access to the certificates used for security. That is, an instance that performs message forwarding is not required to have the capability to decrypt the messages that the instance forwards.Does this imply that message destined for an external service aren't encrypted until they leave the instance? Or does Service Broker figure out that the message is destined for a remote service and therefore applies encryption to the message_body prior to the message hitting the transmission queue on the initiating service?Many thanks, Kevin
I have a question regarding Transparent Data Encryption. I have enabled TDE on a database using the below steps:
1. Create a master key encryption by a password. 2. Create a certificate in the the user database named 'TDE_Test' protected by the master key. 3. Create database encryption key 'TDE_Test' using the certificate created in Step 2. 4. Enable encryption by using the command 'ALTER DATABASE TDE_Test SET ENCRYPTION ON'
I understand that if I need to copy this TDE encrypted database to a different SQL Instance, I have to copy the certificate from the source Instance to the destination Instance.Now my question is, do the Service Master Key and Database Master Key come into the picture here anywhere?
Are these related to TDE in any way? Do I have to take regular backups of the Service Master Key & Database Master Key as part of regular maintenance for the SQL Instance that has a TDE encrypted database?
I am using SQL server 2012 Management studio and I have some confidential data on publisher which is being replicated to subscriber and i want to revoke permission for decryption at publisher end which is only possible using Asymmetric key as it allows only private key to decry-pt the data. But problem which i am facing is,we can not take backup of asymmetric keys which i could restore at subscriber. I do not want to share the private key password with sender. Is there any way to achieve it?
We are unable to login in database due to “The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid. Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.”we have tried to run that selfssl.exe from command prompt followed by below command and am getting the cryptographic error.
I'm trying to encrypt an existing column in my Sql server 2005 Database. The column is a vachar field. How should i go bout with encrpting it without doing any changes in my application code. Neither changing the type of the column field to be encrypted.
Is there any possible way, that while I transferring a table from another table, I can specifically encrypt a column? Is there any step on how can I do this?
We are planning to store the pin number in a sqlserver table. This column has to be encrypted and stored in the database. As new records are added this column needs to encrypted automatically. Please advise me of any tools.
1. Is it possible to encrypt a column "in place" - or do we always need to create a new varbinary column to hold the encrypted column? Some products (NetLib Encryptionizer) seem to be able to encrypt an existing column in place.
2.If you encrypt an existing column to a new varbinary column, it it customary to then ALTER the table and delete the existing plain-text column?
Are there any UDf's or Xtended stored procs available in sql server 2k that can encrypt a column that has the CC #'s or do I need to purchase a 3rd party tool ?
I know that it is possible to encrypt data fields in SQL Server 2005, but I can't find anything about data encryption in the Compact Edition. Is this possible?
I am trying to implement the column encryption on one of the tables, have used the below link as the reference and got stuck at the last step.
[URL] ....
I have completed the following steps so far.
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’
- CREATE CERTIFICATE MyCertificateName WITH SUBJECT = 'A label for this certificate'
- CREATE SYMMETRIC KEY MySymmetricKeyName WITH IDENTITY_VALUE = 'a fairly secure name', ALGORITHM = AES_256,
[Code] .....
Example by using the function
EXEC OpenKeys
-- Encrypting SELECT Encrypt(myColumn) FROM myTable
-- Decrypting SELECT Decrypt(myColumn) FROM myTable
When I ran the last command :
-- Decrypting SELECT Decrypt(myColumn) FROM myTable
I get the following error :
Msg 257, Level 16, State 3, Line 2 Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.
Where will I use the convert function, in decrypt function or in select statement?
Hey, I have 3 columns in a table Ex: Select Column1,Column2,Column from TableName
No. 1. Person A should have permission to read values only (Column1, Column3 of the table) - 2. Person B, should have permission to read only (Column 2).
Here my question is , I have to write one single stored procedure to statisfy both conditions. Which means, if person A execute this stored procedure , he shoud get only column 1 & 3 values . similarly other person b should get column 2 value. Ex: Column 1 - Empid Column 2 - SSN (Only for Top user display) Column 3 - Join Date Person A & B as a SQL or Windows login
I am trying to find the best way to implement a complex column based security. Basically i need to secure data among lot of tables with millions of rows, so that some users may see some columns and others see diferent ones... Whats the best way to implement this kind of procedure securely and flexible?
We have a user who is set to following permission on a DB,
server role: public db permission: db_datareader
But when user tries to use following query on xml column he getting error as below,
SELECT ticket_id FROM dbo.Form WITH(NOLOCK) WHERE LEN(form_document.value('(/Form_Fields/Form_Field[@field_type_desc="Number"]/@field_value)[1]','varchar(100)')) > 0
Error: The EXECUTE permission was denied on the object 'testDB_Form_Schema', database 'testDB', schema 'dbo'.Do i have provide any additional permissions to query xml content ?
I want to perform column level and database level encryption/decryption.... Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms... I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database... Can anybody help me out... If you have Store procedure in SQL for the same then also it ll do... Thanks in advance
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says: Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Is there any possibility to schedule SQL job execution as Windows Security Group? I need to run powershell script through SQL job with one of this group member's permissions.
I have Sql Server Express installed on Vista (service pack 2)
I have Visual Studio 2005 with an application that I'm trying to access it with within a WCF service.
The login ID of the service is added to the database.
The database has remote access turned on.
The ID is granted access to all databases within the server.
The thread is being set with WindowsProvider and the services set their thread to WindowsProvider.
The dataserver is set with using Windows Authentication for security.
When I open my connection to the database, though, it reports the typically useless message that the connection is not allowed and that the server may not allow remote connections.
How to I get past this? I've done everything right.
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
I am looking for a way to log all security related events for SQL in Windows Security Log. I am trying to use SCOM for monitoring SQL and I am looking at ways to generate alerts in my SCOM Console for specific events in SQL e.g. A table is deleted, user is modified, deleted, etc. Is this possible and if yes how do I achieve the same?
In an environment where there are many initaitors speaking to a central target with frowarders in between, from what i can understand this best policy is to disable encryption on the endpoints, since dialog encryption will be enforced this is all that is really required, is this correct.
If the endpoints used encryption the message would need to be encrypted and decrypted at each forwarder resulting in slower perfromance, where as dialog encryption would only encrypt at the sender and decrypt at the target, so is this the best way to go?
Secondly is it best practice to open a dialog initally and send messages over this dialog for years never ending the conversation? This way the services only have to authenticate eachother once, if there are no reboots etc that is of course.
I would think performance wise sending each message and ending the conversation each time is a much greater overhead ? So would it be best practice to keep dialogs open and keep sending messages ?
Initally when i was learning service broker i thought that one must send a message and end the dialog until the next message, but i think the other way is the best option ?
I'm designing a distributed application where I will have SQL Server 2005 distributed databases replicating data to my central hub which is again a SQL Server 2005 database using SQL Service Broker. Data will be sent from the central hub to the distributed sites and vice versa. I need to authenticate the communication and also secure the communication by encrypting the messages. Which security shall I use? Where do I configure the type of security being used? What is the difference between transport security Vs dialogue security - Full security model?
