I want to store a function in SQL Server database that when called during insertion, encrypts a value and then stores encrypted text in the column.
Is there any built in Encrypting and Hashing mechanism in SQL 2005.
Hi All,
I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.
If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.
Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?
Thanks In Advance,
Madhu
Is it possible to encrypt data in one column. I always thought it wasn't possible but I need to know for sure.
View 1 Replies View Related
I am trying to encrypt a column, and then do a select and decrypt the results.
I believe that I am encrypting the data properly, but whenever I try and do a select the column comes back as NULL.
If I do a select without trying to decrypt the column (select * from table) I get the following:
Name | Dept | Encrypted_Info
-----------------------------------------------
scott | it | 0x38383131
If I try and decrypt it I get this
Name | Dept | Encrypted_Info
-----------------------------------------------
scott | it | NULL
This is the query that I put together to create the database, table, DB Master Key, Cert, encrypt the column, and then select and decrypt the result:
create database QA_Encrypted
Go
create table QA_Encrypted.dbo.Enc_Table1
(UserName varchar(50),
Dept varchar(50),
Encrypted_Info varbinary(MAX))
Go
use QA_Encrypted
Go
if not exists (select * from sys.symmetric_keys where symmetric_key_id = 101)
create Master Key encryption by password = 'Pa55w0rd'
Go
Create Certificate QACert
with subject = 'Certificate for Testing'
Go
Create symmetric Key QAEncryptionKey with Algorithm = DES
Encryption by certificate QACert
Go
open symmetric key QAEncryptionKey decryption by certificate QACert
update QA_Encrypted.dbo.Enc_Table1 set Encrypted_Info = EncryptByKey(Key_GUID('QAEncryptionKey'), 'AUTHENTICATOR')
Go
use QA_Encrypted
insert into dbo.enc_table1 values ('scott', 'it', Convert(varbinary,'8811'))
OPEN Symmetric Key QAEncryptionKey
Decryption by CERTIFICATE QACert
select * from dbo.Enc_Table1
Select Username, Dept, Convert(varchar, DecryptByKey(Encrypted_Info)) as "decrypted"
from dbo.Enc_Table1
Any help would be greatly appreciated.
Thanks in advance
I need to encrypt one column of a Sql Server 7 database. I would like to use some sort of function to accomplish this so regular reporting tools can grab the encrypted data.
View 1 Replies View Relatedencrypting a column in a sql server database. I have userid and password columns that I think should have protection on them. My thought was to encrypt these 2 columns. What happens if someone needs to know the actual values of a userid and password? Is encryption the right way to secure this data?
View 2 Replies View Relatedhi i am doing the project in sql sserver 2005
so i want help from u
the following topics i want
"HOW TO ENCRYPT THE DATA IN COLUMN OR ROW IN sql server 2005"
PL REPLY SOON
MY MAIL ID IS
sarunprasadmtechis@gmail.com
if u get any idea pl send mail to the above address pl..
arunprasad
I have a database that is the publisher in transactional replication and also part of an availability group. I have put the pertinent certificates on all of the involved servers, and it is encrypted on all servers and operated as expected. However, we are adding additional security for personal data and we have targeted columns in multiple tables for column encryption. I have a master key and certificates that are stored in the master database. Following an example where I am to create the database master key:
-- Create database Key
USE encrypt_test;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO
But when I try to create a certificate on the database:
-- Create self signed certificate
USE encrypt_test;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO
It get the following:
Msg 15151, Level 16, State 1, Line 1
Cannot find the certificate 'Certificate1', because it does not exist or you do not have permission.
Can I add a database certificate to an already TDE enabled database and if not to I create the symmetric key through the certificate located on the master database? And how will that effect decrypting the column values in stored procedures and function on the user database?
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
Hi,
I have a SSIS project where I am transferring data from DB2 table to SQL Server table. There is a column called REC_ID which I need to encrypt before we store it in SQL Server. Now, SQL Server has buildin encryption functionality and we need to use that as there are views that will decrypt this column and give data to authenticated users.
So, the question is, is there anyway that I can encrypt the column data in my SSIS package using my target SQL server database key and using SQL server encryptbykey function while transferring?
Thanks,
Ujjaval
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. It works effectively however i am capturing the movement of multiple fields. A simple example, for Order lines, i have a price, a discount and a date. I am capturing a 001, 010, 100 respectively for each change.
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View Related
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
Good afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
Hi,
I have got a table where i want to display sum of count(Column1), count(Column2) in another column.How can this be done?
for example
SELECT SUM(Count(pxInsName)+Count(pxFlowName)) AS "pySummaryCount(1)" , Count(pxInsName) AS "pySummaryCount(2)" , Count(pxFlowName) AS "pySummaryCount(3)" , pxAssignedOrg AS "pxAssignedOrg" , pxAssignedOrgDiv AS "pxAssignedOrgDiv" , pxAssignedOrgUnit AS "pxAssignedOrgUnit" FROM pc_assign_worklist WHERE pxObjClass = ? GROUP BY pxAssignedOrg , pxAssignedOrgDiv , pxAssignedOrgUnit ORDER BY 'pySummaryCount(1)' DESC
But sum function can not be used on aggregate function.
Is there any other way.
Hi Folks, Im new to SQL, and I am trying to do the following:
I have a table Documents with DocID, Path and FileName.
A second table Keywords has KwdID, KeywordString
A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.
I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.
I found a nice sample here
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
which shows how to return just the comma delimited string itself:
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString
FROM DocumentKeywords
WHERE KwdID = 1
SELECT @List
I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?
Thanks
I have a sql 2005 DB that stores personal information including Social Security #. How can i encrypt the SSN in Sql and then Decrypt it in a asp.net 2.0 page?Thanks
View 3 Replies View Related
I am calling my rports from the server useing URL method.I need to encript this URL please help me.
I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.
One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)
Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way :-)
Thanks!
Hi all,
Can anyone help me regarding encrypting a password.
While i put my password in a table, it shd be encrypted form so that none of the user shd able
view that and for admin purpose i shd able to view, that is, i shd able to decrypt the password
show to the user. The password is stored in a table as a field.
waiting for ur replies.
thanks in advance.
regards,
krr
Is there any way by which a SQL Server database can be encrypted.
Regrads
Hi:
I was wondering if any of you had any idea about how I could achieve to encrypt a certain field(s) or a whole table in one of the table in my Database. Somewhat, I want to hide the password field from everyone's curious eye.
IF possible please also reply to this email :
alexiscloutier@digisoftcorp.com
Hi,We want to encrypt MS Sql Server data files - .mdf and .ldf withlogged in user certificate and make sure that MS Sql Server service(running as Local System Account) can decrypt it.Is it possible to encrypt data files with a certificate that residesin logged in user'scert store and also MS SQL Server Service 'service account's certstore?You can access 'service account's cert store through mmc -
Quote:
Hello,I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I havebeen working with SQL Server, Building ASp WebSites for many years now.I am by no means an expert - nor have I had ANY formal training. So ebarwith me if my questions seem elementary...I have some questions regarding sensitive data and encryption.There is a project that is headed my way were the social security numberis being used as the unique identifier for an account. I have alwaysused as identity column as a unique identifier. What would be the prosand cons of using the SSN as a unique identifier?#1 How do I go about encrypting the number to store in the DB. Is thisdone within SQL Server? Or before the data is inserted?#2 Is it possible to use an encrypted field as a unique identifier?My gut tells me to use the identity column , encrypt the SSN and not useit as any part of an identifier.Thank You for your Help.. Happy New Year!Please Reply to the Newsgroup.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 12 Replies View Related