Best practice advice please

I'm looking for advice on the best way to stop stored procedures and CLR assemblies from being

copied from their originally installed server to a different server, for the same company or even copied

to another company.

Are there established ways for achieving this level of protection.

Also, I was hoping that encrypting stored procedures would be a 100% reliable way to stop

malicious copying of the code. But I have read that this is not the case. Any advice in this

area would also be appreciated.

Thanks

Steve

Procedure encryption, like the locks on the doors to your home/abode, are only designed to keep out the 'merely curious' -not those determined to be inside.

It is very difficult to prevent a dba (sysadmin) from accessing the code from stored procedures and assemblies. Easily obtainable 'decryption' tools will allow you to see the the code in unencrypted form -even if the code is from an obfuscated assembly (SQL 2005).

Best Practice is to control who has sysadmin access to the servers, all other users have finely 'tumed' permissions, and have a well crafted 'acceptable use' policy in place.

|||

Thanks Arnie.

In my using of Profiler, I could not see the code for encrypted SPs, so I am surprised to hear that the code from an obfuscated assembly can be seen.

Any how, I'll have to think some more.

Thank you

Steve

|||Well the assembly itself cannot be seen and the statements neither, it will only by the following visible in Profiler:

-- Encrypted text

But in addition to Arnie, the "encryption" in SQL Server 2005 is weak and not build for hiding the procedure code in a secure manner. It is just to add an additional barrier to prevent the users to easily read the procedure code. it is doable to read the code but needs additional knowledge about decryption of the procedure code (or even just a good experience to handle the google search :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

best place to declare/load XSL for use in CLR StoredProc?

How to efficiently load XSL documents which will be used in a CLR SP. I want
to avoid loading it every time the SP is invoked.
Thanks,
ChrisHello ChrisHarrington" charrington-at-activeinterface.com,

> How to efficiently load XSL documents which will be used in a CLR SP.
> I want to avoid loading it every time the SP is invoked.
How about in an table, passing it as a parameter?
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for responding. This CLR SP stuff in new to me. Could you elaborate
on your suggestion? Do you mean storing the XSL in an XML column in a table?
Thanks,
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7452758c85c8a7518f6a0@.news.microsoft.com...
> Hello ChrisHarrington" charrington-at-activeinterface.com,
>
> How about in an table, passing it as a parameter?
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||> Thanks for responding. This CLR SP stuff in new to me. Could you
> elaborate on your suggestion? Do you mean storing the XSL in an XML column
in a
> table?
I'll post an example in my blog shortly.
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks - that would be very helpful.
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7454618c85ce91388c3f0@.news.microsoft.com...
> in a
> I'll post an example in my blog shortly.
>
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello Chris,
Been a bit too busy to post, but here's the gist of it. First, we need a
SQLCLR function that actually does the transformation. Here's that:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
= -1)]
public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
{
// on null return null, just in case.
if (Data.IsNull || StyleSheet.IsNull)
return SqlXml.Null;
// Buffer the transformed xml
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
// Load and transform
XslCompiledTransform ctx = new XslCompiledTransform(false);
ctx.Load(StyleSheet.CreateReader());
ctx.Transform(Data.CreateReader(), xw);
// return the result, assuming XML compliant output
return new SqlXml(ms);
}
}
}
here's some code I wrote to test that:
declare @.d xml, @.s xml
select @.d = (select productID as '@.dbid',ProductNumber as '@.productID',Name
as 'name',Color as 'color',ListPrice as 'listPrice',Size as 'Size',SizeUnitM
easureCode
as 'sizeCode',style as 'style' from adventureworks.production.product where
not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag =
1 for xml path('product'),root('products'),element
s xsinil,type)
select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
as p
select dbo.ApplyTransform(@.d,@.s)
the "c:\simple.xlst" is left as an excercise for the reader.
Cheers,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thanks for the code sample. But what I am really stumped on is how to have
the xsl available as a class static so that it doesn't have to be loaded and
compiled every time the SP is invoked. Any thoughts?
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad745b3b8c85df2965c9c40@.news.microsoft.com...
> Hello Chris,
> Been a bit too busy to post, but here's the gist of it. First, we need a
> SQLCLR function that actually does the transformation. Here's that:
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Xml;
> using System.Xml.Xsl;
> using System.IO;
> namespace DM.Examples
> {
> public partial class XmlLibrary
> {
> [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
> false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
> [return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
> = -1)]
> public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
> {
> // on null return null, just in case.
> if (Data.IsNull || StyleSheet.IsNull)
> return SqlXml.Null;
> // Buffer the transformed xml
> MemoryStream ms = new MemoryStream();
> XmlWriter xw = XmlWriter.Create(ms);
> // Load and transform
> XslCompiledTransform ctx = new XslCompiledTransform(false);
> ctx.Load(StyleSheet.CreateReader());
> ctx.Transform(Data.CreateReader(), xw);
> // return the result, assuming XML compliant output
> return new SqlXml(ms);
> }
> }
> }
> here's some code I wrote to test that:
> declare @.d xml, @.s xml
> select @.d = (select productID as '@.dbid',ProductNumber as
> '@.productID',Name as 'name',Color as 'color',ListPrice as 'listPrice',Size
> as 'Size',SizeUnitMeasureCode as 'sizeCode',style as 'style' from
> adventureworks.production.product where
> not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag = 1
> for xml path('product'),root('products'),element
s xsinil,type)
> select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
> as p
> select dbo.ApplyTransform(@.d,@.s)
> the "c:\simple.xlst" is left as an excercise for the reader.
> Cheers,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>