How can we protect sensitive data (custom properties) in a custom connection manager or a custom data flow component?
The SSIS Books Online indicates in the "Security Considerations for Integration Services" page that "If you write custom tasks, connection managers, or data flow components, you can specify which properties should be treated as sensitive by Integration Services". But how to do it programmatically? Are there any attributes that can be applied on custom properties?
I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are
Firstname varchar(50) not null Latname varchar(50) not null dob datetime not null addressline1 varchar(50) null adressline2 varchar(50) null city varchar(50) null postalcode varchar(20) null
I want to display null values as nulls empty as empty not null as numbers.
I've got a customer who thinks their data should be sepeated and reside on two seperate databases in case one is compromised. Now I've never heard of anyone doing this and would like comments from the users here as to why this should/(shoult not) be done.
I'm assuming that they want to keep their general info e.g. name and address on one database and keep other (medical info) on the other db.
None of our db's are outside of the firewalls so to me this makes no sense but I would like feedback.
I'm developing custom SSIS task (control flow component) which offers usert to choose ADO.NET connection.
I want to use this connection in my code and access SQL server.
There is no any problem unless connection is with IntegratedSecurity = true.
But, when user chooses to set username and password, I can not access password because it is not present in the connection string as sensitive information.
Package.DTSProtectionLevel is set to EncryptAllWithUserKey
I need to programmaticaly access to password, but I don't know which class to use from DTS (SSIS) object model.
I am facing problems as the data in sqlserver is not case sensitive. The data in parent key may be capital/lower case and the same data in the child table may be lower/capital case. While migrating the data from sqlserver database to other databases(like oracle) its giving error as the data not found in parent key, though the data found in parent table . This is just because the case sensitive in oracle. But according to my knowledge its better if the sqlserver also supports data case sensitive.
Take a small example Need a table to store all the alphabets in a table the table structure is
CREATE TABLE [dbo].[ALPHABET] ( [Alphabet] [varchar] (1) NOT NULL , [Description] [varchar] (50) NOT NULL ) GO
The data is
Alphabet Description
a Small a
b Small b
C Capital C
A Capital A
sqlserver wont allow to insert data 'A' and gives error "voilation of primary key", though a & A are different according to this table.
I tried with NVARCHAR datatype also. The same problem here also. Sqlserver atleast should support data case sensitivity for NVARCHAR datatype as this can store different languages. May be in other languages the entire meaning may be differ with case differences. Even in english language some words meaning will differ with case differences. For reference can refer english dictionary
S/W Technologies : C#.Net 2005, ASP.Net 2005, SQL Server 2005 Greetings everyone, Heres my code for login verification, which is written in the login button click. SqlCommand cmd = new SqlCommand("Select uid,pass from UserRegistration where uid='" + txtuname.Text + "' and pass='" + txtpass.Text + "'", con); con.Open(); dr = cmd.ExecuteReader(); if (dr.HasRows) { <code......> } Now, my problem is, the SQL Server 2005 is fetching rows without checking case. For e.g. if I enter a password as "MYSTERY" or "mystery" which is stored as "Mystery" in the database. The datareader shows positive in HasRows property. So, Can someone suggest me how to fetch case sensitive data from SQL SERVER 2005. Thanks.
I transferred data from Oracle to sql server 2005. Now what i want is to make data in the tables case-sensitive .(it has to be data inside the tables only and not table and column names). what i tried is :
alter database test collate Latin1_General_CS_AS
But to my horror it made the tables name case-sensitive . Plz help me out asap.
I have a large table with email column. I need to grant select access to this email column to many users but the actual email should not be visible to those users. I thought of following options:
1.Create an indexed view with that column encrypted and then grant access to users.While searching by email, their search text will also be encrypted and then matched with view column.Problem with this is that I have to create indexed view because most searches will be on Email column and table size is pretty big,20 m records. I don't prefer indexed view in general.
2. Add another column to table with encrypted email and grant access to that column to users. Problem with this is that it will increase table size and i'll need one more index.
I have a requirement to read an encrypted file as a data source. I am not allowed to save an unencrypted text file version on disc at any time for any length of time, therefore I created a custom source component that reads an encrypted csv file, decrypts it, and then passes each row of data to the pipeline and ultimately to an ole data destination. Basically it is just a text file reader with an added class that adds functionality that decrypts the file before the component sets columns or reads rows.
The custom component, “Encrypted File Source”, has a custom property “encryptionkey” with the encryption required flag set to true (code below) and is declared as eligible to be set in the expressions.
IDTSCustomProperty100 EncryptionKey = ComponentMetaData.CustomPropertyCollection.New(); EncryptionKey.Name = "EncryptionKey"; EncryptionKey.Description = "Secure String key value to decrypt the file"; EncryptionKey.Value = string.Empty; EncryptionKey.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; EncryptionKey.EncryptionRequired = true;
I want to be able to set the password for the encrypted file in the SQL Agent job that executes the SSIS project. This means I have an environment with a variable, “DataPassword”, that is set to sensitive. It maps to a Project parameter in the SQL Agent job that is also set to sensitive. And I now I want to access that sensitive Project Password inside my data flow, specifically in the Encrypted File source task that I created and set my EncryptionKey to that Project Parameter.
The problem is that SSIS says.
"expression cannot be evaluated. ... The Expression will not be evaluated because it contains sensitive parameter value "$Project::DataFilePassord" . Verify that the expression is used properly and that it portects sensitive information" ((Microsoft.DataTransformationsServices.Controls) "<v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f">
[Code] ....
I am using SQL Server 2012, on a windows 7 box with VS2010 premium.
Hi there ,1. i have a database and i want to encrypt my passwords before storing my records in a database plus i will later on would require to authenticate my user so again i have to encrypt the string provided by him to compare it with my encrypted password in database below is my code , i dont know how to do it , plz help 2. one thing more i am storing IP addresses of my users as a "varchar" is there a better method to do it , if yes plz help me try { SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["projectConnectionString"].ConnectionString; SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT *From User_Info", myConnection); SqlCommandBuilder builder = new SqlCommandBuilder(myAdapter); DataSet myDataset = new DataSet(); myAdapter.Fill(myDataset, "User_Info"); //Adding New Row in User_Info Table DataRow myRow = myDataset.Tables["User_Info"].NewRow(); myRow["user_name"] = this.user_name.Text; myRow["password"] = this.password.Text; // shoule be encrypted //not known till now how to do it myRow["name"] = this.name.Text; myRow["ip_address"] = this.ip_address.Text; myDataset.Tables["User_Info"].Rows.Add(myRow); myAdapter.Update(myDataset, "User_Info"); myConnection.Close(); myConnection.Dispose(); } catch (Exception ex) { this.error.Text = "Error ocurred in Creating User : " + ex.Message; }
This problem is a bit weird but I'm just wondering if anybody else experienced this.
I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.
Just wondering if anybody else experienced this problem and of course if there's a way to solve it.
I'm very new new at this. I'm try to deploy a report model and got this message. I have no idea what its going on about.
Can anyone help me?
Aku
------------------------------
System.Web.Services.Protocols.SoapException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: Bad Data. (Exception from HRESULT: 0x80090005) --- End of inner exception stack trace --- --- End of inner exception stack trace --- --- End of inner exception stack trace --- --- End of inner exception stack trace --- --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.DataProtection.ProtectData(Byte[] unprotectedData, String tag) at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage() at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String storedProcedureName) at Microsoft.ReportingServices.Library.DBInterface.GetAllConfigurationInfo() at Microsoft.ReportingServices.Library.RSService.GetSystemProperties(Property[] requestedProperties) at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values) (System.Web.Services)
selectLogin.CommandText = "SELECT sid, type from STAFF Where sid= '" + txtId.Text + "' and pwd= '" + txtPwd.Text + "' ";
//open connectin for execution sqlConnect.Open();
//instantiate the SqlDataReader reader SqlDataReader loginReader = selectLogin.ExecuteReader();
//try and catch SqlException error try { if(loginReader.Read()) {
// check whether the user is the role of administrator or operator // I use GetValue(1) i.e. type field from the above select statement // if "O' then go operator page, else go to administrator page. if (loginReader.GetValue(1).ToString().ToUpper().Equals("O")) { Server.Transfer("//SMS/LoginUser/SuccessLoginOper.aspx");
} else if (loginReader.GetValue(1).ToString().ToUpper().Equals("A")) { Server.Transfer("//SMS/LoginUser/SuccessLoginAdmin.aspx"); }
}
else { //clear content of textbox and display error message txtId.Text=""; txtPwd.Text=""; lblLoginFail.Visible = true; lblLoginFail.Text="Login Failed!<br>" + "Ensure that ID and Password are correct!"; }
} catch (SqlException se) { if (se.Number == 17) { lblLoginFail.Visible = true; lblLoginFail.Text = "Could not connect to the database"; }
I want to change my database character set and I use database SQL Server 6.5. At the first time I install database, I use charset type to case sensitive. Now, I want to change this charset from case sensitive to un case sensitive.
I hope somebody want to trasnfer knowledge about it. Thanks for attention.
I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not! How can I set my SQL Server 2000 to be case sensitive as well?
Hi all, There is a requirement to perform a case-sensitive search on a column in table. The installation of SQL Server is case-insensitive... Eg.: select * from t1 where c1 = 'abcd' should return only rows where c1 = 'abcd' and not 'ABCD' or 'Abcd' or any other.
I understand that this can be done using the CONTAINS predicate using Full-text indexing. select * from t1 where CONTAINS(c1,'abcd')
Is this the right solution to the problem? Has someone had experience implementing this?
Our database is configured as case insensitive. I need to run a query which is case sensitive. Is there a query option or function I can use to compare, taking upper/lower case into consideration?
How do you set the case sensitive option in SQL 2000? If l already have data in the database whats the best way of doing this.l want the selects on the database to be case sensitive?
After all the pain I've been going through with code pages and collation, I was asked how, when sql server does it's joins and predicate searches, how does it actual (internals now) know the an "A" = "a" in an insensitive search?
I'm a bit flummoxed on this one (doesn't take much these days). We have a test SQL database and a prod SQL database configured with the same user name and a complex password.
The password consists of letters and numbers, mixed upper and lower.
What I am seeing, however, is that SQL will permit access even if the user gets the case wrong on the letters. One letter, two letters, all letters. It doesn't matter.
I thought SQL passwords were case sensitive; was I wrong?
Regards,
hmscott
Edit: I should add that I am running SQL 2000, SP3a (hotfix 0818) on Windows 2000 SP4 and that SQL is clustered on two servers in Active/Passive mode (this applies to both Test and Prod).
Hi,I have yet to find an answer for this:I want to do a case-sensitive query using "like" on a table in sql 7.Currently, "like" performs case-insensitive query.I understand that you can use the following query in sql 2000:SELECT *FROM table_xWHERE col1 collate SQL_Latin1_General_CP1_CS_AS LIKE '% AVE %'However, is there a similar method for sql 7?Any answer would be appreciated.Thanks,Jay
Using ADO 2.7, what is the best way to perform a case-sensitive filter? I have seen on other forums where folks have said that the StrComp function can be used inside of the .Filter method, but I haven't been able to get that to work. I am using VB 6 and ADO 2.7, and have a need to perform case sensitive filters. I know I am not the ony one who has needed to do this...
In the process of migrating from SQL Server 2000 to SQL Server 2005, an application that auto-generates reports suddenly started failing. It turned out that the usernames and passwords were being up-cased, and SQL Server authentication on the 2005 box was rejecting the logons. When the app was changed to leave credentials in whatever case they were received, the reports all ran successfully.
Is that case-sensitivity a function of the collation schema on the database? On the entire SQL Server installation?
Either way, how can it be changed to be case-insensitive for such credentials?
