SQL Security :: Enforcing Unique Constraint On A Column Encrypted With Cell Level Encryption?
May 11, 2015
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'm retreiving data from a SAP BW data source. In the dataset designer I can build a query that returns a single value, the data returns successfully in the designer. When I switch to the layout mode I can place the field of the datset in table / matrix / textbox. However when i switch to preview mode or deploy the report and try to see them in Report Manager the report fails to render with the following message:
Query Execution Failed for dataset 'XXX'
These columns don't currently have unique values.
Its a single cell datset?!?!
I can successfully retreive data from other SAP Infocubes and queries. I also cannot replicate the error using a SQL Server datasource. The stack trace error looks like this:
w3wp!processing!6!5/9/2007-16:57:41:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. ---> System.ArgumentException: These columns don't currently have unique values. at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint) at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign) at System.Data.DataTable.set_PrimaryKey(DataColumn[] value) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.CreatePrimaryKeys(DataTable table, InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Microsoft.ReportingServices.XmlaClient.IMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.ObjectMetadataCache.Microsoft.ReportingServices.XmlaClient.IObjectCache.Populate() at Microsoft.ReportingServices.XmlaClient.CacheBasedFilteredCollection.PopulateCollection() at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollectionInternal.Find(String index) at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollection.Find(String index) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.FindOptionalPropertyName(String internalColumnName, CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.GenerateColumnInformation(CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader..ctor(AdomdDataReader dataReader, CubeDef cube) at Microsoft.ReportingServices.DataExtensions.SapBw.SapBwCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
Any ideas why this happening and how I can get around it?
Thanks
Sanjay
ps Why would you want to put a unique constraint on a read only single table dataset?
I've implemented and tested cell level security on the cube. It's testing certain level conditions, and returns #N/A (as normal) when the user is not supposed to see the cell value. Since I always use .FormattedValue in my reports, works fine in Report Services (and Excel and ProClarity, etc.)
Here's the problem:
When RS parameters encounter this situation, the parameter dataset "breaks" (The following system error occurred: Type mismatch.) This is happening, because the parameter fields (ParameterValue, ParameterCaption, ParameterLevel) are being replaced by #N/A, due to the cell level security. This is happening, because these are actually defined as members, and hence passing through cell level security.
What I need to do is find a way to have these specific members bypass the cell level security, so that the parameter datasets still work. (Failing that, a new way of specifying parameters in MSRS.)
I've tried the following a a cell level security rule, but it doesn't seem to work:
[Measures].CurrentMember is [Measures].[ParameterValue] or [Measures].CurrentMember is [Measures].[ParameterCaption] or [Measures].CurrentMember is [Measures].[ParameterLevel] or [Measures].[Is Visible]
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 a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
I don't immediately find if this is possible but hope someone can give me an answer: is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..) instancetype can be A or B if it is A then instancename must be unique but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
I have a table with one of its column VARBINARY(MAX).
I want to make sure that the values in this VARBINARY(MAX) column is unique. SQL Server doesn;t allow to create Unique Constraint over VARBINARY fields - whats the best workaround for ensuring uniqueness on VARBINARY columns.
I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.
Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?
(1) contains a for loop task (in which all the logic is contained) that loops through a particular folder for excel files WITHIN THE FOR LOOP: (2) pulls data from an excel file into SQL Tables(Data Transformation Task) (3) run stored proc to validate data (Execute SQL TASK) (4)ON SUCCESS of executing the SQL Task (Script Task - move file to success or reject folder based on value returned from sproc) (5)ON FAILURE of executing the stored proc (Script Task - move file to bad format failure)
NOTE: I have modified the MaximumErrorcount property of (1 FOR LOOP) and (3 EXECUTE SQL TASK) and the package itself to 0. In order to deal with badly formatted excel files...I do not want the package to stop for every missing tab in excel file or data entry error. I simply want the badly formatted file to be moved to a special folder
PROBLEM: on failure logic is never executed (I have 2 options after step (3)) on success do step (4) on failure do step (5) step (3) fails...then it simply iterates to the next file step 5 is never executed
Is this because I changed the maximumerrorcount property? What am I doing wrong witht he Precendence Logic?
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 am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
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
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
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?
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.
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
I have a customer who is looking to encrypt their applications connection to the back-end SQL server. I've gotten a valid certificate for the server from a trusted CA, and it's installed on the server (shows up in Personal certificates in the Certificate MMC when selecting the computer) and I can choose it in the drop down on the certificates tab of the SQL Network protocols properties in Configuration Manager.
Now, my question: If I select the certificate but do NOT enable the "Force Encryption" option, the client will be able to request encryption on their end, and the connection will then be encrypted. If the client (other applications) do NOT request encryption, they will still be able to connect, but their connection will be un-encrypted. URL....
Lastly, I could've swore I had found some information indicating that some methods of establishing a connection to SQL can fail when Force Encryption is enabled, as the method doesn't support SSL encryption.
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID.
The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?
Would anyone care to discuss the best way to accomplish Row and Cell Level Authorization using VS 2008, ASP.NET 2.0 and SQL Server 2005.
We are looking at a fairly complex system which displays multiple listings on one page. Each listing contains a variable number of columns from many different tables. Rows and columns from different tables may have different levels of authorization. There may be 10's of thousands of users. Each user may see and or edit any number of listings and any listing could potentially be available for read or write to any number of users.
Users authorize other users to view or edit listings. All users have the right to assign other users but are limited in the level of access they can provide to others.
From a speed and programming effort point of view, would it be best to use a single high level login to SQLServer and manage all authorization on the Web Server side or is it best to create all the security accounts on the SQLSever and use its build in facilities to manage the appropriate level of control required for this application.
Does anyone have any insight as to whether the new data encryption features of SQL Server 2005 work when the database is set to compatibility level 80?
