I need to store payroll information (pay rates) in my data warehouse.
I've search on field level encryption and most the stuff i find is for
passwords, I.E. not reversable. I need to protect the payrate field
from dba's and sa's, but I need to allow the authorized users to see
the contents.
Any ideas? Does 2005 handle this?
I don't have a web app, per se, that access's this information. however
I am using Cognos' Report Net.
thanks in advance
Rob
hi,
Please help me in designing one of our payroll policies. i have been trying but unable to find any solution.
i have data in 2 tables :
1. attendance (empid, date, hours, leaveallowed)
2. holidays (date,type) (4 types of holidays like national, weekly offs etc)
Policy: if any employee is not present on both days - the day before and after the holiday then leave allowed is false
attendace table contains the records for all employees who are present and i have to add rows for missing dates where leaveallowed will be false (hours will be zero).
eg. if any employee is absent on friday and monday then leaveallowed for saturday and sunday will be false.
i have tried many things but the best i have is following query that uses cross join with holidays table after adding rows for all dates in holidays (holidays table now looks as calendar)
Code:
SELECT DRVD.empid,DRVD.calendardate,DRVD.holidaytype,
attendance.workhours
FROM attendance RIGHT OUTER JOIN
(SELECT * FROM employees CROSS JOIN calendar
WHERE calendardate < getdate()) DRVD
ON attendance.empid=DRVD.empid
AND attendance.workdate=DRVD.calendardate
thanks
Hi,
We are currently trying to make payroll data stored on an SQL server available via a secured web site for our employees. This will allow them to view their direct deposit information by visiting a particular .asp page running on our web site. In order to enable this, we must create a connection to the SQL server. In order to verify the connection, we have created an Access ADP that will allow us to test our connection parameters. Once we can connect in Access we can write a connection string for use in our .asp pages. We believe that the following connection parameters should connect to the SQL server:
Server Name: hsa1.***.com:1433
User Name: payroll-web
Password: ***
Database: HSA
We have already created the payroll-web user name on the SQL server and given it permissions to access the HSA database running on the SQL server. However, when we create an Access ADP using these settings we receive the following error:
"Test failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(ParseConnectParams())]Invalid connection."
At this point we are unaware of the cause of this error and have tried several different workarounds, none of which have worked:
Replaced hsa1.***.com:1433 with hsa1.***.com, 1433
Replaced payroll-web and *** with Administrator and ***
Removed HSA
Selected NT/Challenge Response Security instead of provising a user name and password
Does anyone have any ideas as to (i.) why this error is occuring and (ii.) what we can do to solve it and connect to the SQL Server using an Access ADP? Thanks so much for your help!
Warm Regards,
Kristopher A. Tillery
Director of E-Commerce, Harvard Student Agencies
Email: tillery@fas.harvard.edu
Phone: +1 603 205 0228
Post: 359 Quincy Mail Center, Cambridge MA
Web: http://www.hsa.net
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Hi all
I have got an application developed on SQL7 which I have migrated to SQL2K.
It handles authentication internally and therefore stores usernames & passwords in a local table. The passwords are stored encrypted utilizing a function called "encrypt" taking the password in clear-text and returning a varbinary. When a user logs on his password input is encrypted the same way and compared to the stored password. So far so good...
Problem: On SQL2K the encrypt function returns a different result as it does on SQL7., which reults in authentication failures.
Unfortunately I cannot find any documentation about the encrypt function e.g. in BOL. Furthermore I cannot ask the developer who introduced the function anymore.
Hi Guys.
I am looking for TSQL code for RC4 encryption and decryption.
I found VBcode for RC4 encryption, it will take atleast 2 days for me to go thru that and rewrite the whole code in SQL.
If anyone has the code, please provide me.
(I know sql does accept extended ASCII characters)
Thanks
-MAK
Hello,
I do have one application in VC++ as a Front End & Sql Server 2005 as a Back End.
i want to store my username's password in encrypted format, means no one can able to see the password.
Ex:- if i enter password as a "xyz" then it should be save as a some encrypted [base 64 encoding scheme] format.
Can any one help me out?
Thanks
Prashant Hirani
Hi All,
In SQL 2000, i need to Export Data,so that datashould be encrypted.When i try to import that in any database it should authenticate the user and should get decrypted.IZAT Possible.
Can any one help?
Thanks,
Karthik
Is there a way to encrypt one column of data in a table in SQL Server 2000? Thanks!
-Dave
Hi,
In SQL2000 i need to Encryptdata when I export data using DTS.LikeWise I should when i import data I should authenticate that user and decrypt that.
Can any one help?
Thanks,
Karthik
Hi folks,
I'm trying to encrypt a small token of data on my client c# application and have it decrypted by SQL on the server side, the problem is i cannot find articles on the subject. I don;t really want to get involved with certificates but base the system on a simple symmetric key that is shared by both parties.
I'm attempting to use the TRIPLE_DES algorithm on both sides and thus far have used the decryptbypassphrase on the server side with the data encrypted on the client side by .Net with no initialization vector setup.
If anyone can recommend any articles or have example (client and server side) code for this situation it would be greatly appreciated.
Many thanks in advance
Simon
Does anyone know what my .net app guys need to share with me if encryption was done in the .net app but decryption needs to occur in certain sql queries? I read about master keys, certificates, symmeteric keys, algorithms etc but dont know how that stuff would carry over from the framework into sql. All I know is that the algorithm is AES_256, they must be adding authentication to the encryption and i know the hash algorithm, and symmetric keys are involved. Will they be sharing certain kinds of files with me that need to be registered in the db? Will I have to use CLR if I want the two worlds to come together?
View 4 Replies View Related
Hi,
I found that I could encrypt StoredProcedures in Sql Server Express but have no idea about encrypting views. Any idea?
Hi,
I would like to encrypt and decrypt password in my own tables
select stuff
from mytable
where password = decrypt('ackdo$$y')
How can I do this? Is there any decrypt or crypt function?
thanks,
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View RelatedHi,I have a .net application and i added a code that encrypts data saved in database. However, there is already data in the fields that was entered before this change.I know need to check if the values in those fields are encrypted and if not i need to encrypt them.How can I perform such a check and update the relevant data?I use TrippleDES in .net to encrypt/decrypt the data.Thanks
View 1 Replies View RelatedHi,
I would like to know that how can I encrypt a stored procedure, so that it can not be decrypt by me or not by any one ? I have tried using the SQL encrypt,but there is a decrypt command which decrypt the same. I do not want any one to decrypt without a password or encrypt the stored procedure so that it can never be decrypted.
Thanking you,
Regards..Jay
Hi,
Please help!!!!!!!
Is there are ways to encrypt data in the table (SQL Server 7) and then retrieve (decrypt) this information?
Thanks
I'm running SQL 70 SP 3 on Nt4.
We store passwords of users of our website. They need to be autenticated and based on that it gives them access to what they are entitled. But its not like NT or server authentication.
This has been setup so that we have a user table and it stores the password. However, it stores it in plain text. Is there any way I can encrypt this field so it is unreadable? Is there a property or a datatype that I can't find? Is there a way to simulate the encryption?
Any ideas or help are appreciated.
Thanks
Kelsey
I am setting up several isql job in Enterprise Manager and as a cmdexec job I need to pass the user id and password( -P). I shtere any way not to pass this as text and perhaps hide/encrypt it since any one who opens the task scheduler and looks at the job can read the password?
Thanks.
DAvid Spaisman
Does SQL Server 2000 provide any data encryption/decryption functionality so that certain fields (e.g. SSN, Age and Salary) will be encrypted before writing into the table and decrypted once loading out of the table?
J827
We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.
I'd like to encrypt datas with reversible function in one field of a table
Is there a function able to do this kind of work in SQLServer V7 or 2000 ?
hello guys! i have a question hope you'll help me..how can i encrypt the data that is stored in my password field everytime i insert value to it and decrypt it if i want to retrieve it? thanks in advance!!
View 7 Replies View Relatedhi guyz!! is it posible to ecnrypt data everytime i insert it to a table and decrypt it everytime I select it using the MS SQL 2005 alone?
like for example i have this query statement below
insert username,password users values ('daimous','my_password')
what i want is every time i insert a value to the password column that value should be encrypted first.
select username,password from users
everytime i select the value of the password column should decrypted.
Thanks in advance!!!
I have been trying to create a certificate for use with SQL2005. I found openSSL to create a cert but I am not sure how to use it.
When I go into SQL Config Manager / Protocol Properties / Certificate Tab... I do not see any certificates. The list is empty. Where are these certs pulled from and how can I create one on my own?
Here are the Reqs:
Certificate Requirements
For SQL Server 2005 to load a SSL certificate, the certificate must meet the following conditions:
The certificate must be in either the local computer certificate store or the current user certificate store.
The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
How might I encrypt a stored procedure in SQL server.
In sybase this is done with SP_HIDETEXT but SQL server doesn't appear to have this.
Is there another way?
Thanks,
Any t-sql query that will encrypt several databases without using the use DB_name? I am trying to create a SP that will look for the flag if 0 then encrypt.
SELECT name AS 'Database_Name' ,
is_encrypted AS 'Encryption Enabled'
FROM sys.databases
where database_id not in (1,2,3,4)
and is_encrypted = 0
However I cannot find a query that will set encryption on with having to use the USE DB_NAME clause.
How to encrypt the Text Of Stored Procedure
Pls Sir Give me Small Example
Yaman
Hi
I am using sql server 2005, I want to encrypt data and i am using Symmetric key.
In Symmetric key encrypt the varchar , varbinary data encrypt but for integer it show the error.
so please suggest me can we encrypt the integer data.
if yes then how ?
In below example if we use integer then encrypt it will show error.
--1) Create master key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Asit'
--2) Create certificate.
CREATE CERTIFICATE CertificateTest2
WITH SUBJECT ='CertificateTest2'
--3) Create SYMMETRIC KEY .
CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertificateTest2
--4) Open SYMMETRIC KEY .
OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE CertificateTest2;
--5) create table.
create table SYMMETRIC1
(
id varchar(100) not null primary key ,
name varbinary(MAX) not null,
card_num varbinary(MAX) not null
);
--6)SEA THE RECORDS
SELECT * FROM SYMMETRIC1
--7)INSERT RECORD IN TABLE
INSERT INTO SYMMETRIC1(ID,NAME,CARD_NUM)VALUES (
EncryptByKey( key_guid('TestSymKey'),'2',1,'SQL Server') ,
EncryptByKey( key_guid('TestSymKey'),'Asit sinha',1,'SQL Server'),
EncryptByKey( key_guid('TestSymKey'),'11',1,'SQL Server') )
--8)FETCH ENCRYPTED RECORD FROM TABLE AND DECRYPT THE VALUE
SELECT
convert(varchar(max),DecryptByKey(id,1,'SQL Server')) as ID,
convert(varchar(max),DecryptByKey(Name,1,'SQL Server')) as Name,
convert(varchar(max),DecryptByKey(card_num,1,'SQL Server')) as Card_Num
FROM SYMMETRIC1
--9)SEA THE RECORDS
SELECT * FROM SYMMETRIC1
Asit Sinha