SQL 2012 :: Allowing Access To DECRYPTBYKEY Function
Nov 6, 2015
How to grant users the right to use the DECRYPTBYKEY function to decrypt the data. I have seen some people talk about using a stored procedure or view to surface the decrypted data, but how would you implement that when trying to pull back a single dataset? It would be best to use an inline function to allow the row to be returned decrypted, but opening the keys isn't allowed in the function construct.
So, I know I have to be missing something, but how do you let basic users (db_reader types) decrypt the data they need based on a custom database role? What do I need to give the user permission to?
My setup is simple:
- I have my SMK
- I have a DMK encrypted by password
- I have my self signed certificate
- I have my symmetric Key encrypted by the certificate
View 0 Replies
ADVERTISEMENT
Apr 6, 2004
Hey all.
I'm trying to set up SQL Server so that people with Enterprise Mgr can create a DB registration to their DB only (sql.yoursite.com). Are there any tutorials out there for doing this?
Thanks for the help!
View 3 Replies
View Related
Nov 13, 2003
I am trying to make a connection to sql server using sspi (windows authentication), however, I do not wish to use the built in tokenauthentication system. Administrators are worried about somebody using an unattended logged on computer to gain access to the database. So I am left with using sql server authentication (which I am not fond of doing, more password administration) or what I would like to do is use windows authentication but make the user enter thier windows password prior to logging into sql server. Is there a way? Thanks in Advance.
Kent
View 2 Replies
View Related
Jul 20, 2005
With MS SQL 2000 Enterprise Manager, is there a way to allow a user accessto only a few tables, but deny the user access to the rest without having togo to all of the tables and denying access? The database has roughly 50tables, but only 3 should be granted to the new user, so as you can see itwould be a painstaking task to manually do this with the *cough* mouse. Or,if I can run some sort of grant script, that would work too. Thank you!
View 2 Replies
View Related
Aug 31, 2006
I have read that it is possible to configure sql server express so that the database can only be accessed through stored procedures. Can anyone tell me how to do this. Many thanks.martin
View 2 Replies
View Related
Nov 6, 2007
I have a SPROC in which I am using a CTE. Turns out, one of the encrypted fields isn't being decrypted properly - I get "system characters" and not NULL values for the encrypted field, meaning the conversion is failing somewhere. I have tried various combinations to try and decrypt the value, but to no avail so far - I have tried decrypting in the CTE alone, tried decrypting in the CTE plus the SELECT that follows the CTE, as well as only the SELECT statement that follows the CTE. If I run the same conversion in regulare SELECT, outside the CTE, the value comes out just fine.
Any thoughts?
Edit: By the way, I am opening the key using the certificate outside the CTE and closing it after the SELECT that follows the CTE.
View 1 Replies
View Related
Jan 12, 2007
Hi
I'm having some issues using the decryptbykey method via multiple connections. When I run the below test script simultaneously on two machines the sum function is always less then the known amount (ie 14945490 and 36382777). Does anyone know of any locking method or alternative way to sum an encrypted column?
Thanks in advance
Waz
open symmetric key HR01 decryption by password='yes'
DECLARE @Bonus decimal
DECLARE @Salary decimal
DECLARE @Errors int
DECLARE @Success int
DECLARE @LoopCount int
SET @Errors = 0
SET @Success = 0
SET @LoopCount = 0
WHILE (@LoopCount < 40)
BEGIN
SELECT
@Bonus = SUM(convert(float,convert(varchar(80),decryptbykey(Bonus)))),
@Salary = SUM(convert(float,convert(varchar(80),decryptbykey(Salary))))
FROM ChallengeEmployee
WHERE ChallengeID = 5
IF(@Bonus <> 14945490 OR @Salary <> 36382777)
BEGIN
PRINT 'Bonus ' + CAST(@Bonus AS varchar(80))
PRINT 'Salary ' + CAST(@Salary AS varchar(80))
SET @Errors = @Errors + 1
END
ELSE
SET @Success = @Success + 1
SET @LoopCount = @LoopCount + 1
END
PRINT 'Finish'
PRINT 'Errors ' + CAST(@Errors AS varchar(80))
PRINT 'Success ' + CAST(@Success AS varchar(80))
close symmetric key HR01
View 13 Replies
View Related
Jan 21, 2008
The DecryptByKey function occasionally returns null even though the EncryptByKey function retuned a non-null value. The problem only occurs for a subset of rows returned by a single select and every time the script is executed, a different set of rows is affected by the problem. Occasionally all fields get encrypted/decrypted successfully, but this is rare.
It seems that the EncryptByKey function occasionally returns a value that can not be decrypted at a later point in time.
I am running on Windows XP Professional SP 2 with SQL Server 9.0.3042.
I have included a sample of the code below.
Thank you,
Mike
CREATE FUNCTION [dbo].[encrypt_text]
(
@input_text varchar(255)
)
RETURNS varbinary(8000)
AS
BEGIN
RETURN EncryptByKey(Key_GUID('eia_key'), @input_text)
END
CREATE FUNCTION decrypt_text
(
@input_text varbinary(8000)
)
RETURNS varchar(255)
AS
BEGIN
return convert(varchar(255),DecryptByKey(@input_text))
END
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = N'eia_key')
DROP SYMMETRIC KEY eia_key
CREATE SYMMETRIC KEY eia_key
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = '???'
OPEN SYMMETRIC KEY eia_key DECRYPTION BY PASSWORD = '???'
execute util_print 'Deleting data'
execute ld_delete_lips_data
execute util_print 'Loading data'
set nocount on
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (1, 'TERM', 0, 0)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (2, '0 - 2', 0, 2)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (3, '2 - 5', 2, 5)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (4, '5 - 10', 5, 10)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (5, '10+', 10, null)
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (1, dbo.encrypt_text('3 Month'), dbo.encrypt_text('Blended'))
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (2, dbo.encrypt_text('1 Year'), dbo.encrypt_text('Fundamental'))
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (3, dbo.encrypt_text('Technical'), dbo.encrypt_text('Technical'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (1, dbo.encrypt_text('Low'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (2, dbo.encrypt_text('Median'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (3, dbo.encrypt_text('High'))
execute util_reseed_ident 'asset_classes', 0
execute util_execute_sql 'insert into asset_classes default values', 11
insert into sectors (sector_id, sector_name)
values (1, dbo.encrypt_text('Sovereign'))
insert into sectors (sector_id, sector_name)
values (2, dbo.encrypt_text('Inflation Linked'))
insert into sectors (sector_id, sector_name)
values (3, dbo.encrypt_text('Quasi & Foreign Government'))
insert into sectors (sector_id, sector_name)
values (4, dbo.encrypt_text('Securitized/Collateralized'))
insert into sectors (sector_id, sector_name)
values (5, dbo.encrypt_text('Corporate'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (6, dbo.encrypt_text('AAA'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (7, dbo.encrypt_text('AA'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (8, dbo.encrypt_text('A'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (9, dbo.encrypt_text('BBB'))
insert into sectors (sector_id, sector_name)
values (10, dbo.encrypt_text('High Yield'))
insert into sectors (sector_id, sector_name)
values (11, dbo.encrypt_text('Emerging Debt'))
set nocount off
insert into currencies (currency_id, currency_name, currency_code)
select CurrencyID, dbo.encrypt_text(CurrencyName), dbo.encrypt_text(CurrencyCode)
from lips_import..Currencies
View 3 Replies
View Related
Aug 1, 2005
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies
View Related
Oct 5, 2005
What is the counterpart of this function when using SQL Server 2000? Im
getting this error "System.Data.SqlClient.SqlException: 'Val' is not a
recognized function name"
What do I have to change in my queryString?
SELECT ASP_MainForm.UserID, Val([FormNo]) AS Expr1,
ASP_MainForm.DateCreated, ASP_MainForm.DateNeeded,
ASP_MainForm.FormStatus, ASP_MainForm.Print, ASP_MainForm.PRNo,
ASP_MainForm.ForUse, [FirstName]+' '+[LastName] AS CompName FROM
ASP_MainForm INNER JOIN CEN_USERS ON ASP_MainForm.UserID =
CEN_USERS.UserBadgeNo WHERE
(((ASP_MainForm.FormStatus)<>'Approved') AND
((ASP_MainForm.ForUse)<>'Test')) ORDER BY Expr1 DESC
Pls help thanks
View 2 Replies
View Related
Jan 29, 2001
Hello there...
I am looking for the function that is the same as InStr in Access for SQL server. I have a column that has format like this.. Lastname,Firstname Middlename...
This column doesn't separate each one of them. However I need to separate Lastname and Firstname and Middlename.. I was told that in Access there is function(InStr) that can find a position of comma and separate it as Lastname like that....
I was searching BOL but I couldn't find like this function in SQL Server..
So I need help:-))))
Because everybody has a different length of the lastname, I have a problem.
I can not use SUBSTRING or LEFT or RIGHT because of the varying position of comma ...
Thanks in advance
Jay
View 1 Replies
View Related
Oct 18, 2004
Hello All -
I have a database in Access with the following fields:
Portfolio Code
Trade ID <- Uniquie
Trade Shares
Order Decision Price
Trade Execution Price
Trade Date
I want to retrieve total traded market value for the 2nd quarter (months 4, 5 and 6) for specific portfolio's. When I retrieve a list of all individual trades (by trade ID which is unique for each), Trade Shares, Order Decision Price and Trade Execution Price and do the Multiplying in Excel (i.e. Order Decision Price * Trade Shares and Trade Execution Price * Trade Shares), the final aggregate total looks correct. However, when I try to do the calculation in Excel and group it so I do not get a list of all trades, just the total Traded Market Value ( using Sum([Order Decision Price]*[Trade Shares]) and Sum([Trade Execution Price]*[Trade Shares]) ) in design view as Expression, the total traded market value is larger than what it was in the first step when the calc was done in Excel. It seems that some of the reported values are exactly double of what I get while doing the calc in Excel.
This is only happening in the 2002 database. When I use the Sum method in all other databases, the results are 100% on.
Any ideas??
Example of the SQL created by design view:
SELECT Sum([Order Decision Price]*[Trade Shares]) AS Expr1, Sum([Trade Execution Price]*[Trade Shares]) AS Expr2
FROM TradeHist
WHERE (((TradeHist.[Portfolio Code])="852" Or (TradeHist.[Portfolio Code])="2CM" Or (TradeHist.[Portfolio Code])="2CN" Or (TradeHist.[Portfolio Code])="2WA") AND ((Month([Trade Date]))=4 Or (Month([Trade Date]))=5 Or (Month([Trade Date]))=6));
View 14 Replies
View Related
Mar 10, 2004
Does anyone know if you can call an Access function from DTS?
I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.
Suggestions?
View 2 Replies
View Related
Jun 29, 2006
I normally use MS ACCESS vs MS SQL,, which has a left() and right()function. I need to use MS SQL for this project but I am not familiarwith it. I have read a few books, but can not figure out how to dothis. Please help.If I need to compare the first 4 letters of a field, with the firstfour letters of another field, how can I do this?Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4)(MS SQL does not have left() and right() functions)Please help.In addition, I have a CSV file with data like 10.20, which I importinrto a numberic field. Unforunately the value gets changed to 10.It's seems to get rounded. How can I fix this.The import SQL I use is....BULK INSERT dbo.tableFROM 'c:MYDATA.CSVWITH(FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = '')Thank you in advance!!!
View 9 Replies
View Related
Jul 20, 2005
I'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.
View 2 Replies
View Related
Sep 19, 2014
How Choose function in SQL is useful in a table. Any example with a simple table and how it can be useful for any particular column in a table
View 1 Replies
View Related
Jan 2, 2002
Hi,
I am moving the database built in access to Sql 7 and i am unable to find any subsitute of format function of Access in sql. Please help me out ot find a suitable solution of it.
Thanks
View 1 Replies
View Related
Oct 15, 2015
if I have table XXXX with columns a,b,c,d,e,f,g,h,i and I need a function or stored procedure.If I use SELECT a,b,c,d from XXXX and the function returns the result set with columns e,f,g,h,i only Means the columns used in Select must not be included in the result set.
View 10 Replies
View Related
Oct 16, 2006
I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
View 10 Replies
View Related
Jun 8, 2015
I am trying to get the details of an xml. For this I am using below code. I am not able to get the desired output.I want to add 1 more columns AnimalID and DetailID.I want these column to interlink the other values. Below is the desired output.
declare @x xml
set @x=
'<config>
<Animal name="Baboon" ref="Ape">
<detail name="Ape detail" ref="Monkey" typo="animal" required="true">
[Code] ....
View 7 Replies
View Related
Nov 30, 2013
I want to use max() function and I want to read the input of this function from another database(its name is exhibitor). like below :
select @LastDate=MAX([exhibitor.dbo.Maintable.LastUpdate])but I have error below
Msg 207, Level 16, State 1, Procedure Exec_List, Line 131
Invalid column name 'exhibitor.dbo.Maintable.LastUpdate'.
View 2 Replies
View Related
Jan 19, 2014
I have a scalar function, which calculates the similarity of two strings. I use the following query, to compare the entries of one table against the value 'Test' and return the entries, which have a value > 50:
;WITH cte1 AS (
SELECT b.FirstName,
(SELECT fn_similarity('Test', b.FirstName)) AS [Value],
b.LastName
FROM [AdventureWorks2012].[Person].[Person] b
)
SELECT *
FROM cte1
WHERE [Value] > 50.00
ORDER BY [Value] DESC
Now I want to use this query against the first 50 entries of the [Person] table, so that the resultset includes all the values of the first 50 persons and the entries, which are similar to them.
At the moment I use a WHILE-loop and write the five single resultsets in a temporary table. Is there another way / a better way, maybe via a join?
View 9 Replies
View Related
Nov 10, 2014
I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4 'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me?
SELECT
TT.PartNumber
,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]
,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])
FROM dbo.vt_TroubleTicket TT
WHERE TT.Closed = '-1'
and TT.DateClosed between '1/1/2013' and '1/1/2014'
and (TT.PartNumber = '12345')
GROUP BY TT.PartNumber
View 3 Replies
View Related
Nov 13, 2014
I have setup CDC on 50 tables and then in one SP I’m calling all cdc function like below issue is I'm getting error “an insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes ... .” as error is not mentioning for which capture instance I'm getting this error so not able to find.
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old')
How to find which capture instance is failing?
View 2 Replies
View Related
Mar 16, 2007
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies
View Related
Mar 7, 2001
I am used to working in Access and just recently became somewhate proficient using custom functions in modules.
I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access?
Thanks for your help.
Mike
View 1 Replies
View Related
Jul 28, 2006
I have a combo box named [myControl] on an Access form that I can use to select/enter a site name (ABC). There is a button on the same form that runs a report. The underlying record source for the report is a SQL Function. Is there an easy way to pass the combo box value to the SQL Function so that only records for site 'ABC' are displayed in the report? I tried Forms![myForm]![myControl] in the criteria box of the Function and it did not work. THANKS!
View 3 Replies
View Related
Apr 26, 2006
This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible?
I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF?
I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible.
Thanks in advance,
Sean
View 4 Replies
View Related
Jan 18, 2014
I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.need two seperate functions for city and amt columns..need to also display the parent-description
--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]
[code]...
View 8 Replies
View Related
Mar 18, 2014
I want to create a custom bitwise OR aggregate function.
I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
I have followed this post to implement amedian aggregate function [URL] ..... but there is a lot of code (not sure what is really needed in my case).
View 9 Replies
View Related
Apr 4, 2014
I am having a hard time getting a variable recognized in a function. The variable is not being seen properly in the charindex function.
@ExtType contains = X
@PhoneNo contains = +1 (202) 123-9876 X012
select @intPos = charindex(@ExtType,Upper(@PhoneNo))
View 1 Replies
View Related
Apr 10, 2014
I have multiple sites trying to communicate with a SQL Server 2012 Express database at another remote site. At one site I am unable to connect to the remote server. If I try to use my program I get this message:
System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The token supplied to the function is invalid)
If I try to connect using SSMS I get the same error.I have been unable to find any reference to this message on the internet.
View 3 Replies
View Related
Jan 6, 2015
I have the following query that supposes to merge multiple result in a single one and put it into a temporary table:
SELECT DISTINCT [AlphaExtension],
STUFF((SELECT A.[NoteText] + '< BR />' FROM #temp A
WHERE A.[AlphaExtension]=B.[AlphaExtension]
FOR XML PATH('')),1,1,'') As [NoteText]
FROM #temp B
GROUP BY [AlphaExtension], [NoteText]
It is working fine unless by a simple detail. If you look at the second line of the query you will see that I am stuffing together a < BR /> tag (break line) because the contents of the field is going to be spitted directly to the screen and I want that the multiple results be displayed in different lines.
OK, the issue is that it is stuffing & lt ; BR / & gt ; instead < BR /> and therefore the browser is displaying the tag instead to break a line.
View 2 Replies
View Related
