Sensitive Data
Nov 3, 2006
Hi all,
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?
Thanks.
Pascal
View 6 Replies
ADVERTISEMENT
Sep 1, 2006
Do you know of an code example that would find what databases and what tables have sensitive data such as ssn, name of individual, etc.
View 1 Replies
View Related
Oct 21, 2014
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.
View 4 Replies
View Related
Nov 28, 2007
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.
thanks.
View 3 Replies
View Related
Jan 18, 2007
Hello,
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.
Any help?
Thanks, Borko
View 1 Replies
View Related
Aug 28, 2006
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
View 1 Replies
View Related
Apr 7, 2008
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.
View 3 Replies
View Related
Feb 25, 2008
Hii
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.
Thanx in advance
Supriya
View 18 Replies
View Related
May 12, 2015
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.
View 9 Replies
View Related
Oct 8, 2015
when prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?
View 5 Replies
View Related
Feb 11, 2014
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.
View 4 Replies
View Related
Jan 7, 2007
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; }
View 3 Replies
View Related
Dec 14, 2006
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.
Thanks.
View 2 Replies
View Related
May 25, 2007
Hi every one,
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)
------------------------------
BUTTONS:
OK
------------------------------
View 38 Replies
View Related
Mar 21, 2004
Dear everyone,
I am doing Login webform (C# .NET web application) with SQL Server 2000.
The staff table is to store authenticated user info.
But when I test it, I found that the password can be case insensitive, i.e. 'A0001' should be correct password, but 'a0001' can allow login.
Could anyone tell me how to solve this problem??
Thanks you very much!!
private void btnLogin_Click(object sender, System.EventArgs e)
{
//instantiate SQL connection
SqlConnection sqlConnect = new SqlConnection(connectStg);
SqlCommand selectLogin = sqlConnect.CreateCommand();
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";
}
else
{
lblLoginFail.Visible = true;
lblLoginFail.Text = se.Message;
}
}
//close SqlDataReader and SqlConnection
loginReader.Close();
sqlConnect.Close();
View 5 Replies
View Related
Jul 6, 2005
Hi
how can i use the sensive case in a select field from table where fild='GhhY' ?
View 2 Replies
View Related
Aug 15, 2002
are SQL Server 7 table names, column names case sensitive?
View 2 Replies
View Related
Feb 13, 2001
In SQL Server 7.0, how do you SELECT a column which has values beginning with only lower case letters
can anyone advise?
View 1 Replies
View Related
Feb 15, 2001
Hi,
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.
Regards,
Susan
View 1 Replies
View Related
Apr 24, 2007
Hi,
I am using SQL Server 2000.
How to make case sensitive of a database.
Eg :
Should work
Select * from Employees
Select * from Employees where Title like 'S%'
Should not work
select * from employees
select * from employees where title like 's%'
Thanks in advance.
View 7 Replies
View Related
Feb 5, 2005
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?
View 1 Replies
View Related
Aug 21, 2001
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?
Thanks in advance.
-Praveena
View 1 Replies
View Related
Jul 26, 2000
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?
Regards,
Gavin Patterson
View 7 Replies
View Related
Mar 10, 2000
Is it possible in SQL Server 7.0 sp1 to have a
password that is case-sensitive on a case-insensitive
installation of SQL server?
Toni
View 1 Replies
View Related
May 20, 2002
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?
View 3 Replies
View Related
Jul 2, 2004
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 didn't have the answer.
Damn, Now I really have to pick up Kelans book.
View 14 Replies
View Related
Oct 27, 2004
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).
View 4 Replies
View Related
Oct 13, 2006
in sql server 2000 or 2003 how can i tell if a database is case sensitive or not??
View 2 Replies
View Related
Aug 31, 2006
hi friends, is it possible to make sqlserver case sensitive?
i mean is there any options to set while installation?
thank you very much
View 3 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Aug 7, 2007
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...
As always, your time is appreciated.
View 4 Replies
View Related
Jul 19, 2007
We did some hardware work on our server, after reinstalling the OS and SQL we reattached our DB.
Now it appears as if SQL is set up as case sensitive and our application is crashing.
How do I make SQL Case insensitive once it is installed?
View 1 Replies
View Related
Nov 8, 2007
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?
Thnx!
Phil
View 3 Replies
View Related
