Decryption within an application

I need to encrypt one column of data in a single table and I pretty much
have all the operations figured out, including maintaining both the
encrpyted data and a one way hash for searches. I have a view which
decrypts the data properly when the symmetric key has been opened (and
obviously returns null when the key is not open).
I want the view to return the decrypted data only when the user is accessing
the database from a single application. This application maintains a single
database connection per session. My thought was to open the key when the
database connection is established by the application and close it when the
application exits, thereby granting access only through the application. Is
that an acceptable practice?
If I do that, should I protect the key with a password that is then compiled
in the application so that I can open the key? This means that every
installation will have a key protected by the same password. Or is there a
better way to do that?
Thanks for any help."Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
>I need to encrypt one column of data in a single table and I pretty much
>have all the operations figured out, including maintaining both the
>encrpyted data and a one way hash for searches. I have a view which
>decrypts the data properly when the symmetric key has been opened (and
>obviously returns null when the key is not open).
> I want the view to return the decrypted data only when the user is
> accessing the database from a single application. This application
> maintains a single database connection per session. My thought was to
> open the key when the database connection is established by the
> application and close it when the application exits, thereby granting
> access only through the application. Is that an acceptable practice?
> If I do that, should I protect the key with a password that is then
> compiled in the application so that I can open the key? This means that
> every installation will have a key protected by the same password. Or is
> there a better way to do that?
> Thanks for any help.
Well, when the key is opened it's specific to a session. So you could have
several sessions opening up the same key simultaneously and I wouldn't think
you'd encounter any problems. Of course you will probably want to do some
thorough testing to be sure, and also make sure you don't take a performance
hit there. I wouldn't recommend storing the key hard-coded in your
application. How about using the Automatic Key Management feature of SQL
2005? The only real downside to it is that all sysadmins can then decrypt
your data (if that's a concern for you - it is for some folks).|||I'm not neccesarily opposed to the sysadmin being able to decrypt the data,
but I don't want any other user outside of the application to have access to
the data. So what I can't figure out (even with SS key management) is how
to open the key only when connecting from the application, unless I compile
a password into the code.
Any thoughts on that would be helpful.
Thanks.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Well, when the key is opened it's specific to a session. So you could
> have several sessions opening up the same key simultaneously and I
> wouldn't think you'd encounter any problems. Of course you will probably
> want to do some thorough testing to be sure, and also make sure you don't
> take a performance hit there. I wouldn't recommend storing the key
> hard-coded in your application. How about using the Automatic Key
> Management feature of SQL 2005? The only real downside to it is that all
> sysadmins can then decrypt your data (if that's a concern for you - it is
> for some folks).
>|||With automatic key management you should be able to connect to the
application and open the symmetric keys without a password. You can use
GRANT to grant permissions to users on your keys, certificates, etc. Here's
an article with some samples that demonstrate encryption/decryption without
passwords, thanks to automatic key management:
[url]http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp[/
url]
You might also want to look into the DecryptByKeyAutoAsymKey and
DecryptByKeyAutoCert functions that combine the DecryptBy... functions with
OPEN SYMMETRIC KEY automatically.
"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
> I'm not neccesarily opposed to the sysadmin being able to decrypt the
> data, but I don't want any other user outside of the application to have
> access to the data. So what I can't figure out (even with SS key
> management) is how to open the key only when connecting from the
> application, unless I compile a password into the code.
> Any thoughts on that would be helpful.
> Thanks.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
>|||Thanks so much for your help, but I must be dense. If I grant permission to
the users or use automatic key management, then it seems to me that the data
can be encrypted outside of my application by a non-sa user. If I only want
the application to display the unencrypted data, I can't see how this
automatic approach works.
That is why I took the approach of having the application open the key.
Sort of like the old application-role security.
But I would love to find a better way.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Od0T8%23amHHA.4772@.TK2MSFTNGP05.phx.gbl...
> With automatic key management you should be able to connect to the
> application and open the symmetric keys without a password. You can use
> GRANT to grant permissions to users on your keys, certificates, etc.
> Here's an article with some samples that demonstrate encryption/decryption
> without passwords, thanks to automatic key management:
> http://www.sqlservercentral.com/col...ion.asp

> You might also want to look into the DecryptByKeyAutoAsymKey and
> DecryptByKeyAutoCert functions that combine the DecryptBy... functions
> with OPEN SYMMETRIC KEY automatically.
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23C1qbs$mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Thanks so much for your help, but I must be dense. If I grant permission
> to the users or use automatic key management, then it seems to me that the
> data can be encrypted outside of my application by a non-sa user. If I
> only want the application to display the unencrypted data, I can't see how
> this automatic approach works.
Anyone who has the username and password used by the application to log into
the database would have the ability to decrypt the encrypted data.
Alternatively, if a password is stored in the application, anyone with a hex
editor could decrypt the encrypted data outside of the application. The
only way I can think of to force decryption only through the application
would be to encrypt only in the application. But then you take on the
responsibility of encryption key management yourself. I don't know of any
magic bullet to ensure that data encrypted using SQL Server can only be
accessed via a specific front-end application interface.

> That is why I took the approach of having the application open the key.
> Sort of like the old application-role security.
Anyone with a hex editor could conceivably locate a password stored in an
application and use it to decrypt data. Linking it to a specific Windows
login puts the burden of encryption key management back on the operating
system. That's basically the main difference; do you want to manage your
own passwords, or do you want to let Windows and SQL Server manage your
passwords?

> But I would love to find a better way.
Biometrics?

Declare Scalar Variable??

I've got a report that is pretty simple but for some reason I keep getting
the following error when I try to run it:
An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set "Dataset 1"
Must declare the scalar variable "@.TABLENAME".
This is the actual dataset I'm trying to run:
EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
This is set up as COMMAND TYPE of TEXT.
Here's the actual stored procedure being called:
ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
----
-- Description: Report all transactions from a given trace table by SQL_ID
-- Revision History:
----
@.TABLENAME varchar(128),
@.SQL_ID int,
@.DB_ID int,
@.Sort varchar(20) = 'CPU'
as
set nocount on
--DECLARE @.Table VARCHAR(128)
--Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
getdate()), 110)
exec ('
select StartTime, Reads, CPU, Duration, spid, [SQL] = convert( varchar(4000), substring( TextData, 1, 4000 ) )
from [' + @.TABLENAME + '] t
join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
where i.id = ' + @.SQL_ID + '
and i.databaseID = ' + @.DB_ID)-- + '
--order by ' + @.Sort + 'desc
--')
GO
I've got another report that is literally the exact same thing, except there
is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
also able to run the stored procedure by itself just fine.
If anyone has any idea as to what the issue may be, that would be fantastic!!
Thanks!On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
wrote:
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be fantastic!!
> Thanks!
I don't think you have the syntax correct on the Reporting Services
side.
This link outlines it:
http://msdn2.microsoft.com/en-us/library/aa337435.aspx
Let me know if this is what you're looking for. I have some scripts I
use to pass parameters into stored procedures at home. I can take a
look into it if the link isn't clear or if it doesn't work.|||I'm using the exact same syntax throughtout my project and all the reports
work fine. For example, this is the syntax in another report I'm using:
EXEC dbo.Report_TSQL_By_ID @.SQL_ID, @.DB_ID
This report works fine with no problems at all...
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||...and the link here is addressing the issue of binding input parameters to
user defined functions...unfirtunately that's not what I'm doing.
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||Is there a reason you are not using a command type of stored procedure? If
you do this then RS automatically determines the parameters and the
parameter data type and creates the report parameters for you. That would
solve your problem.
But, given what you have below the issue is that for whatever reason the
query parameter @.TABLENAME is not mapped to your report parameter. On the
dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
parameter is mapped to the report parameter.
This error is what you get when this mapping has not occured.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
>
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except
> there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be
> fantastic!!
> Thanks!
>
>
>|||Thanks!
I actually discovered the problem about five minutes after I posted my
question!
"Bruce L-C [MVP]" wrote:
> Is there a reason you are not using a command type of stored procedure? If
> you do this then RS automatically determines the parameters and the
> parameter data type and creates the report parameters for you. That would
> solve your problem.
> But, given what you have below the issue is that for whatever reason the
> query parameter @.TABLENAME is not mapped to your report parameter. On the
> dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
> parameter is mapped to the report parameter.
> This error is what you get when this mapping has not occured.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except
> > there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> > I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be
> > fantastic!!
> >
> > Thanks!
> >
> >
> >
> >
> >
> >
>
>

Debugging acting weird

Hi!
I have VS2005 RC1 and SQL Server 2005 September CTP installed on the same machine.
I'm writing some pretty simple C# stored procedures and I'd like to be able to debug them with with the VS2005 IDE. However, something's not working.
When I set a breakpoint and I run the code (F5), it turns white with a red border informing me that "the breakpoint will not currently be hit. No symbols have been loaded for this document". Obviously, those breakpoints are not hit and I cannot debug exceptions either.
I made sure the connection the project is linked to has "ApplicationDebugging" and "Allow SQL/CLR Debugging" turned on.
I'm probably missing a very simple step to get this working, but Google didn't help me this time. So that's why I come here begging for your help!
Thanks in advance!
Carl

Hi Carl,

Have you executed the following TSQL code to enable SQLCLR on the server?

Create a new Stored Procedure in VS, then add the following:

exec sp_configure 'clr enabled',1
reconfigure

If this doesnt work, could you answer the following questions:

1, Are you the running as the machine admin?
2, Are you able to debug a regular TSQL Stored Procedure?
3, Could you send me the text from the output window.

Thanks

Richard Cook
VS SQL Debugger QA

Debugging acting weird

Hi!
I have VS2005 RC1 and SQL Server 2005 September CTP installed on the same machine.
I'm writing some pretty simple C# stored procedures and I'd like to be able to debug them with with the VS2005 IDE. However, something's not working.
When I set a breakpoint and I run the code (F5), it turns white with a red border informing me that "the breakpoint will not currently be hit. No symbols have been loaded for this document". Obviously, those breakpoints are not hit and I cannot debug exceptions either.
I made sure the connection the project is linked to has "ApplicationDebugging" and "Allow SQL/CLR Debugging" turned on.
I'm probably missing a very simple step to get this working, but Google didn't help me this time. So that's why I come here begging for your help!
Thanks in advance!
Carl

Hi Carl,

Have you executed the following TSQL code to enable SQLCLR on the server?

Create a new Stored Procedure in VS, then add the following:

exec sp_configure 'clr enabled',1
reconfigure

If this doesnt work, could you answer the following questions:

1, Are you the running as the machine admin?
2, Are you able to debug a regular TSQL Stored Procedure?
3, Could you send me the text from the output window.

Thanks

Richard Cook
VS SQL Debugger QA