Encrypting Stored Procedure Code

Hi,
Is it possible to encrypt the code within a stored procedure in
Microsoft SQL Server?
My example is:
I've written a stored procedure. I don't want anyone to be able to
view the contents/code within this stored procedure unless I allow them
to see what is in it.
Thanks,
Darrin> Is it possible to encrypt the code within a stored procedure in
> Microsoft SQL Server?
Sure, but it is not very secure. A google search will yield plenty of
decryption algorithms. For example:
http://searchsqlserver.techtarget.c...1056869,00.html|||Once you encrypt a stored procedure you can not decrypt it (within SQL
Server)
So you can't show it to people who you want to show it to
example
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
But like Aaron said this is very weak, there are some vb apps out there
that will decrypt this
http://sqlservercode.blogspot.com/|||I know there are decryptors out there for SQL2000 but do you know of nay tha
t
have been written for SQL2005?
We encrypt our procs mainly as a safeguard against accidentally altering
them but do need a decryptor when we need to edit them. Our upgrade to
SQL2005 will be delayed untill we have a decryptor.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sure, but it is not very secure. A google search will yield plenty of
> decryption algorithms. For example:
> [url]http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html[/url
]
>
>|||>I know there are decryptors out there for SQL2000 but do you know of nay
>that
> have been written for SQL2005?
I will admit that I have only spent 5 minutes looking, but I have yet to
find where the encrypted text is stored, since sys.sql_modules.definition,
sys.syscomments.ctext and object_definition() all return NULL. My guess is,
to make the encoding a little more obscure, that they stuff this into
mssqlsystemresource, or hide it in some obscure system view. So, you may be
able to get to it, you may not.

> We encrypt our procs mainly as a safeguard against accidentally altering
> them but do need a decryptor when we need to edit them.
Isn't that what source control is for? And doesn't that defeat the purpose
of encrypting them in the first place? If someone can accidentally alter a
production procedure, they can also do accidentally after using a decryption
method to view the text. Encryption does not, and will never, solve the
problem of lack of adherence to proper process. My suggestion is to correct
the process.
A|||Thanks for the response. Yes, I fully appreciate the importance of source
control and am confident that our dev department use it properly.
As a production DBA, though, looking after 100+ SQL Servers, when called at
2am to fix a performance "issue", the Decryptor is essential as you dont hav
e
the time to delve into VSS. Also its essential when you need to compare an
existing proc to a proc in Source Safe.
With the move to SQL2005, we will, as suggested, need to look at our
process. It may mean moving away from encrypting procs.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> I will admit that I have only spent 5 minutes looking, but I have yet to
> find where the encrypted text is stored, since sys.sql_modules.definition,
> sys.syscomments.ctext and object_definition() all return NULL. My guess i
s,
> to make the encoding a little more obscure, that they stuff this into
> mssqlsystemresource, or hide it in some obscure system view. So, you may
be
> able to get to it, you may not.
>
> Isn't that what source control is for? And doesn't that defeat the purpos
e
> of encrypting them in the first place? If someone can accidentally alter
a
> production procedure, they can also do accidentally after using a decrypti
on
> method to view the text. Encryption does not, and will never, solve the
> problem of lack of adherence to proper process. My suggestion is to corre
ct
> the process.
> A
>
>

Encrypting Backups

Is there any way to encrypt a backup written to a tape drive?
I know its possible to do when backing up to disk (using an encrypted file
system), but don't see how to achieve the same with tape.
We want to take our backups off-site, and though it would be prudent to
encrypt the data in casae it was lost, stolen, etc.
Thanks,
LenYou might want to look at some 3rd party apps such as SQLLiteSpeed. They
can compress and encrypt the backups.
Andrew J. Kelly SQL MVP
"Len Binns" <binns@.buffalo.nospam.edu> wrote in message
news:OtDqtzEuEHA.1472@.TK2MSFTNGP10.phx.gbl...
> Is there any way to encrypt a backup written to a tape drive?
> I know its possible to do when backing up to disk (using an encrypted file
> system), but don't see how to achieve the same with tape.
> We want to take our backups off-site, and though it would be prudent to
> encrypt the data in casae it was lost, stolen, etc.
> Thanks,
> Len
>|||BTW,
We're using SQL 2000 on Windows Server 2003, Enterprise Edition.
"Len Binns" <binns@.buffalo.nospam.edu> wrote in message
news:OtDqtzEuEHA.1472@.TK2MSFTNGP10.phx.gbl...
> Is there any way to encrypt a backup written to a tape drive?
> I know its possible to do when backing up to disk (using an encrypted file
> system), but don't see how to achieve the same with tape.
> We want to take our backups off-site, and though it would be prudent to
> encrypt the data in casae it was lost, stolen, etc.
> Thanks,
> Len
>|||We were hoping to stay away from 3rd party apps (just one more thing to
break), but we may have to if there is no other solution.
Is there no solution native to SQL Server 2000 and/or Windows Server 2003?
Thanks,
Len
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%236wuA5EuEHA.4040@.tk2msftngp13.phx.gbl...
> You might want to look at some 3rd party apps such as SQLLiteSpeed. They
> can compress and encrypt the backups.
> --
> Andrew J. Kelly SQL MVP
>
> "Len Binns" <binns@.buffalo.nospam.edu> wrote in message
> news:OtDqtzEuEHA.1472@.TK2MSFTNGP10.phx.gbl...
file[vbcol=seagreen]
>|||There are no native solutions that I know of for SQL Server. SQL LiteSpeed
is pretty seamless and will enhance your backup and restore capabilities
tremendously. They will be faster, take up less room and uses RC4
encryption. I know it sounds like I am an Imceda salesman but this product
does what it claims and what you are asking for.
Andrew J. Kelly SQL MVP
"Len Binns" <binns@.buffalo.nospam.edu> wrote in message
news:%23oW4FPFuEHA.3320@.TK2MSFTNGP15.phx.gbl...
> We were hoping to stay away from 3rd party apps (just one more thing to
> break), but we may have to if there is no other solution.
> Is there no solution native to SQL Server 2000 and/or Windows Server 2003?
> Thanks,
> Len
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%236wuA5EuEHA.4040@.tk2msftngp13.phx.gbl...
> file
>

encrypting a text file

I have programs written in sas. The programs themselves can be treated like a text file for this purpose. I need a program that will encrypt my program then when needed decyrypt it storing it into a temp file, run it and then delete it.

any thoughts or anything out there to use.

NicoleAny ability to decrypt means potential weakness thus vulnerability. Look into SQLShield, it may help.

encoding problem

Hi
I use Oracle data source. The report is in Polish. When I try display some text from database or use report parameters, this text is not written correctly. The environment on the machine, where is installed Reporting Services is rather correct, when I am using SQL*PLUS it is ok. I linked Oracle database to MSSQL, I used Query Analyzer, the text was written correct in Polish. What to do?
morganWhat is the report language set to?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"morgan" <morgan@.discussions.microsoft.com> wrote in message
news:9E40E53A-C040-470F-89E2-0E9807BA98D7@.microsoft.com...
> Hi
> I use Oracle data source. The report is in Polish. When I try display some
text from database or use report parameters, this text is not written
correctly. The environment on the machine, where is installed Reporting
Services is rather correct, when I am using SQL*PLUS it is ok. I linked
Oracle database to MSSQL, I used Query Analyzer, the text was written
correct in Polish. What to do?
> morgan|||Polish is the report language. By I used English ( US) too.
morgan
"Ravi Mumulla (Microsoft)" wrote:
> What is the report language set to?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "morgan" <morgan@.discussions.microsoft.com> wrote in message
> news:9E40E53A-C040-470F-89E2-0E9807BA98D7@.microsoft.com...
> > Hi
> >
> > I use Oracle data source. The report is in Polish. When I try display some
> text from database or use report parameters, this text is not written
> correctly. The environment on the machine, where is installed Reporting
> Services is rather correct, when I am using SQL*PLUS it is ok. I linked
> Oracle database to MSSQL, I used Query Analyzer, the text was written
> correct in Polish. What to do?
> >
> > morgan
>
>

Empty Recordset

New to Reporting Services.
I have written a stored procedure which I call from a report built with RS.
Certain parameters being passed to the procedure does not materialize a
record set, therefore, the report comes up blank, with no labels etc. What I
need to do is detect an empty record set, from within the report, and set
default values so the report does not appear blank. I have tried the
IsNothing function within each text boxes expressions, however, it does not
work. Has anyone run into this and if so, would you be so kind to provide a
solution?
Regards,
Fred
Regards,
FredHi,
maybe you can use the NoRows property of your table to display the desired
information?
"FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> New to Reporting Services.
> I have written a stored procedure which I call from a report built with
> RS.
> Certain parameters being passed to the procedure does not materialize a
> record set, therefore, the report comes up blank, with no labels etc. What
> I
> need to do is detect an empty record set, from within the report, and set
> default values so the report does not appear blank. I have tried the
> IsNothing function within each text boxes expressions, however, it does
> not
> work. Has anyone run into this and if so, would you be so kind to provide
> a
> solution?
> Regards,
> Fred
>
> Regards,
> Fred|||Hello Ralph,
Thank you for responding. I am not familiar with the NoRows property, do you
have an example? I tried searching the RS BOL but came up empty.
"Ralph Watermann" wrote:
> Hi,
> maybe you can use the NoRows property of your table to display the desired
> information?
> "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > New to Reporting Services.
> > I have written a stored procedure which I call from a report built with
> > RS.
> > Certain parameters being passed to the procedure does not materialize a
> > record set, therefore, the report comes up blank, with no labels etc. What
> > I
> > need to do is detect an empty record set, from within the report, and set
> > default values so the report does not appear blank. I have tried the
> > IsNothing function within each text boxes expressions, however, it does
> > not
> > work. Has anyone run into this and if so, would you be so kind to provide
> > a
> > solution?
> >
> > Regards,
> > Fred
> >
> >
> > Regards,
> > Fred
>
>|||Hi Fred,
You could modify your stored procedure to test whether or not you
retrieve records depending on the parameters you pass. If the query in your
stored procedure does not return records, then you can pass a single record
with the default values in a Select statement, that you want to show in the
report...
Cheers,
Daniel.
"FredG" wrote:
> Hello Ralph,
> Thank you for responding. I am not familiar with the NoRows property, do you
> have an example? I tried searching the RS BOL but came up empty.
> "Ralph Watermann" wrote:
> > Hi,
> >
> > maybe you can use the NoRows property of your table to display the desired
> > information?
> >
> > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > New to Reporting Services.
> > > I have written a stored procedure which I call from a report built with
> > > RS.
> > > Certain parameters being passed to the procedure does not materialize a
> > > record set, therefore, the report comes up blank, with no labels etc. What
> > > I
> > > need to do is detect an empty record set, from within the report, and set
> > > default values so the report does not appear blank. I have tried the
> > > IsNothing function within each text boxes expressions, however, it does
> > > not
> > > work. Has anyone run into this and if so, would you be so kind to provide
> > > a
> > > solution?
> > >
> > > Regards,
> > > Fred
> > >
> > >
> > > Regards,
> > > Fred
> >
> >
> >|||"NoRows" is an element that is a part of the "Properties" for a table. You
can specify a value in the table's "Properties" (under "Data") for the case
when there is no row to display.
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >|||Hello Daniel,
That is exactly what I did to solve the problem. Thanks
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >