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.
Email
-------
1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
But, how do I work around this?
View 8 Replies
ADVERTISEMENT
May 9, 2007
Hi,
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?
View 1 Replies
View Related
May 27, 2008
Hopefully, someone has figured this out:
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]
Any ideas?
View 4 Replies
View Related
Jan 16, 2008
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
View 1 Replies
View Related
Mar 5, 2008
Greetings all!
How can I enforce a foreign key constraint when I have two tables each one residing on a different database?
Thanks for your help in advance.
View 1 Replies
View Related
Jun 6, 2008
I like to use the management console to design tables, but have
been unable to figure out how to add a unique column constraint.
I have a primary key defined in col1 and want to make col2, char(10),
unique, but keep col1 as the primary key.
I have tried the Check constraint menu path, but do not know how
to write the expression.
If I go along the Indexes/Key path, I am forced to change the primary
key which I do not want to do.
Can anyone help?
TIA, Joe
View 5 Replies
View Related
Jul 16, 2004
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.
View 6 Replies
View Related
May 5, 2014
I am very new to sql and I have got this doubt.
CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };
Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.
if i write
ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;
will drop constraints on both columns which I dont want.
Any query that remove UNIQUE CONSTRAINT only on DESIGN column.
and one more clarification,
ALTER TABLE employee
MODIFY SALARY INT NOT NULL;
ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;
which of the above two is right query to add NOT NULL constraint to the above employee TABLE?
View 1 Replies
View Related
Nov 23, 2007
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 ?
Aeneas.
View 1 Replies
View Related
Oct 29, 2005
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
View 3 Replies
View Related
Nov 17, 2006
Hi,
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.
Thanks,
Loonysan
View 1 Replies
View Related
Jun 17, 2015
I need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it?
View 4 Replies
View Related
Dec 28, 2007
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?
View 4 Replies
View Related
Aug 1, 2007
I have designed an SSIS package that :
(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?
View 4 Replies
View Related
Apr 17, 2001
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.
Appreciate any ideas you can give!
Cheers & TIA
View 1 Replies
View Related
Nov 26, 2015
Is there any way i could apply encryption on a varchar type column without changing its datatype to varbinary.
View 6 Replies
View Related
Apr 5, 2004
Hi all,
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.
Any help would be appreciated.
Thanks,
Amir
View 8 Replies
View Related
Apr 29, 2008
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
Thanks
View 1 Replies
View Related
Jul 17, 2001
Hello all.
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.
tia,
Jeremy
View 1 Replies
View Related
Apr 30, 2008
Hi,
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?
Best Regards,
View 4 Replies
View Related
Oct 7, 2015
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.
View 3 Replies
View Related
Sep 22, 2004
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.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
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?
View 1 Replies
View Related
Apr 1, 2015
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.
View 1 Replies
View Related
May 13, 2008
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?
View 3 Replies
View Related
Mar 7, 2001
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 ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
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?
View 2 Replies
View Related
Feb 4, 2004
SECURITY USING CELL-SECURITY:
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 ?
View 1 Replies
View Related
Dec 13, 2005
I am trying to implement row-security in SQL 2005 but i make a query to make a view
View 14 Replies
View Related
Nov 20, 2007
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.
Rob..
View 4 Replies
View Related
Jun 26, 2007
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?
View 1 Replies
View Related
Nov 2, 2015
The option:
SQL Server Configuration Manager>SQL Server Network Configuration>Protocol for SQL_xxx (right click)>Properties, we can see two Tabs:
Tab 1: Flags
Tab 2: Certificate
If I set the value of Hide Instance= Yes, does "Force Enctyption", need to be set YES as well?
Or Force Enctyption has to be enabled in order to hide instance?
What is the recommend settings?
[URL]
View 3 Replies
View Related
