none
using Set database Trustworthy on for sql clr

    Question

  • hi

    i want my sql clr to write a txt file for error logging i gave my procedure external access and it failed

    i gave my database set trustworthy on for doing this task. its working fine.

    but i fear that should i give the same for my production server also. is it safe to do so ,setting trustworthy on for executing clr in sql server.

    if there any safe alternate for doing this


    Monday, August 20, 2007 11:41 AM

Answers

  • Please have a look at my response in the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2018968&SiteID=1.  There is a sample which shows how you can enable external_access assemblies without using the trustworthy bit.  It is preferrable to use keys rather than the trustworthy bit as the sample illustrates.

     

    --Bonnie

    Monday, August 20, 2007 4:59 PM
  • Manoj,

    I was going to post the following yesterday evening, but for some reason I could not (I absolutely hate web forums, give me NNTP based newsgroups any day - but I guess that is another discussion :-) ). In the meantime you posted this:

     Manoj Manohar wrote:

    Thanks but can u tell me y setting trustworthy is not adviceable if so y microsoft is providing that option to us



    so I guess I should expand my original post a bit. TRUSTWORTHY is not only used by CLR assemblies (in fact, for CLR - as Bonnie mentions - it is not the preferred way, as it can cause ill-advised side effects), but also other scenarios in the database requires TRUSTWORTHY. Look at the following article from MSDN, especially the bit about "Trusting the Authenticator". As for CLR it just happens that using TRUSTWORTHY is a "quick and dirty" way of allowing EXTERNAL ACCESS and UNSAFE.

    Right, so here is what I was trying to post last evening:

     Manoj Manohar wrote:


    i want my sql clr to write a txt file for error logging i gave my procedure external access and it failed

    i gave my database set trustworthy on for doing this task. its working fine.

    but i fear that should i give the same for my production server also. is it safe to do so ,setting trustworthy on for executing clr in sql server.

    if there any safe alternate for doing this


    OK, a couple of things; to execute CLR in the database you do not need to assign an EXTERNAL_ACCESS permission set to the assemblies. You need that if you need to access resources outside of the database server (like in you rexample when you want to write to a file).

    In order to create an assembly with external_access (or unsafe) permission set you nedd extra permissions in the database. This can be achieved by setting the TRUSTWORTHY bit in the database, but as Bonnie says in her reply, this is not a preferred option, as it can cause other un-desired side effects.

    There are other ways of allowing external access or unsafe assemblies in the database. Below follows one variant:

    1. Create a .NET strong name key file by using the sn.exe tool.

    Code Snippet

    sn -k extKey.snk


    2. In the master database create a master key (if one does not already exist:

    Code Snippet

    use master;
    go
    create master key
    encryption by password = 'some_secure_password';


    3. Still in master, create an asymmetric key:

    Code Snippet

    create assymetric key extKey
    from file = 'path_to_the_strong_name_key';


    4. Also in master create a login from the asymmetric key:

    Code Snippet

    create login extLogin
    from asymmetric key extKey;


    5. Give the login just created EXTERNAL ACCESS ASSEMBLY permission

    Code Snippet

    GRANT EXTERNAL ACCESS ASSEMBLY to extLogin;


    6. Build your assembly and sign it with your stong name key (use VS or command line).

    7. Deploy your assembly to the database you want to use it from.

    As your assembly is signed with a strong name key, and the strong name key has an asymmetric key created from it and there is a login created from that symmetric key with the necessary permission set - this will now work.

    As I mentioned before, there are various wasy of doing this, and this is just one way.

    Niels



    Tuesday, August 21, 2007 6:59 AM
    Moderator

All replies

  • sorry i left one more point  i used these queries to make my sql clr working. Sad thing is i made it work on pressure without knowing it use and  cause since i want to give my release. Do i need to do this or any alternative is present

    Step 1 :

    use master

    go

    exec sp_dbcmptlevel 'TEST', 90

    go


    Step 2 :

    USE TEST 

    GO

    ALTER DATABASE TEST SET TRUSTWORTHY ON


    Step 3 :


    USE TEST 

    GO

    CREATE ASSEMBLY SQLCLRTEST FROM

    'C:\JOBS\SQLCLRTEST.dll'

    USE [RGICL_Mode]

    GO

    ALTER ASSEMBLY [SQLCLRTEST]

    WITH PERMISSION_SET = EXTERNAL_ACCESS



    Monday, August 20, 2007 11:53 AM
  • Please have a look at my response in the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2018968&SiteID=1.  There is a sample which shows how you can enable external_access assemblies without using the trustworthy bit.  It is preferrable to use keys rather than the trustworthy bit as the sample illustrates.

     

    --Bonnie

    Monday, August 20, 2007 4:59 PM
  • Thanks but can u tell me y setting trustworthy is not adviceable if so y microsoft is providing that option to us

    Monday, August 20, 2007 9:23 PM
  • Manoj,

    I was going to post the following yesterday evening, but for some reason I could not (I absolutely hate web forums, give me NNTP based newsgroups any day - but I guess that is another discussion :-) ). In the meantime you posted this:

     Manoj Manohar wrote:

    Thanks but can u tell me y setting trustworthy is not adviceable if so y microsoft is providing that option to us



    so I guess I should expand my original post a bit. TRUSTWORTHY is not only used by CLR assemblies (in fact, for CLR - as Bonnie mentions - it is not the preferred way, as it can cause ill-advised side effects), but also other scenarios in the database requires TRUSTWORTHY. Look at the following article from MSDN, especially the bit about "Trusting the Authenticator". As for CLR it just happens that using TRUSTWORTHY is a "quick and dirty" way of allowing EXTERNAL ACCESS and UNSAFE.

    Right, so here is what I was trying to post last evening:

     Manoj Manohar wrote:


    i want my sql clr to write a txt file for error logging i gave my procedure external access and it failed

    i gave my database set trustworthy on for doing this task. its working fine.

    but i fear that should i give the same for my production server also. is it safe to do so ,setting trustworthy on for executing clr in sql server.

    if there any safe alternate for doing this


    OK, a couple of things; to execute CLR in the database you do not need to assign an EXTERNAL_ACCESS permission set to the assemblies. You need that if you need to access resources outside of the database server (like in you rexample when you want to write to a file).

    In order to create an assembly with external_access (or unsafe) permission set you nedd extra permissions in the database. This can be achieved by setting the TRUSTWORTHY bit in the database, but as Bonnie says in her reply, this is not a preferred option, as it can cause other un-desired side effects.

    There are other ways of allowing external access or unsafe assemblies in the database. Below follows one variant:

    1. Create a .NET strong name key file by using the sn.exe tool.

    Code Snippet

    sn -k extKey.snk


    2. In the master database create a master key (if one does not already exist:

    Code Snippet

    use master;
    go
    create master key
    encryption by password = 'some_secure_password';


    3. Still in master, create an asymmetric key:

    Code Snippet

    create assymetric key extKey
    from file = 'path_to_the_strong_name_key';


    4. Also in master create a login from the asymmetric key:

    Code Snippet

    create login extLogin
    from asymmetric key extKey;


    5. Give the login just created EXTERNAL ACCESS ASSEMBLY permission

    Code Snippet

    GRANT EXTERNAL ACCESS ASSEMBLY to extLogin;


    6. Build your assembly and sign it with your stong name key (use VS or command line).

    7. Deploy your assembly to the database you want to use it from.

    As your assembly is signed with a strong name key, and the strong name key has an asymmetric key created from it and there is a login created from that symmetric key with the necessary permission set - this will now work.

    As I mentioned before, there are various wasy of doing this, and this is just one way.

    Niels



    Tuesday, August 21, 2007 6:59 AM
    Moderator
  • Thanks Niels/ Bonnie

    I started loving microsoft forumn great , Hope soon i too become like you guys repling to others queries. Hope those days are not so far.

    Really cool microsoft is providing lot  of ways to do our work in best way and easy too we should select the best. Thanks for the post and your presious time.

    Thank you all
    Tuesday, August 21, 2007 10:00 AM
  • Hi Manoj

     

    As you mentioned I followed the same above steps, but still i am getting the error wile executing the SQL Function

     

    I tried creating the asymmetric key using assembly as well as from strong name key file, but both fails

     

    use master

    CREATE ASSEMBLY SQLCLR FROM 'D:\SQL.dll';

    -- above assembly signed with same key file 'D:\SQLKeyFile.snk';

     

    create master key encryption by password = 'password';

    create asymmetric key SQLKey from assembly SQLCLR;

    OR

    create asymmetric key SQLKey from file = 'D:\SQLKeyFile.snk';

    create login SQLLogin from asymmetric key SQLKey;

    GRANT EXTERNAL ACCESS ASSEMBLY to SQLLogin;

    CREATE FUNCTION dbo.IsValidImageURL

    (

    @paramURL as nvarchar(200)

    )

    RETURNS NVARCHAR(500)

    AS EXTERNAL NAME SQLCLR.[SQL.URLValidator].IsValidImageURL

     

    SELECT dbo.IsValidImageURL('http://forums.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif')

     

    Error Message:

    Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

     

    Can you please help me.

     

    Thanks

    Bhavesh

    Wednesday, May 28, 2008 12:33 PM
  • You have to create the assembly after you create the key and login.  An example with code and screenshots can be seen at the following address.

     

    http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx

     

    You should be able to drop the function, then drop the assembly, and then create the assembly with external access, and then create your function again to make this work.

     

     

    Wednesday, May 28, 2008 12:51 PM
    Moderator
  • i tried the same steps but still no success

     

     

     

    use master

    GO

    create master key encryption by password = 'password';

    GO

    create asymmetric key SQLKey from file = 'D:\SQLKeyFile.snk';

    GO

    create login SQLLogin from asymmetric key SQLKey;

    GO

    GRANT EXTERNAL ACCESS ASSEMBLY to SQLLogin;

    GO 

    CREATE USER SQLUser FOR LOGIN SQLLogin;

    GO

    USE TestCenter

    GO

    CREATE ASSEMBLY SQLCLR FROM 'D:\SQL.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS

    GO

    CREATE FUNCTION IsValidImageURL

    (

    @paramURL as nvarchar(200)

    )

    RETURNS NVARCHAR(500)

    AS EXTERNAL NAME SQLCLR.[SQL.URLValidator].IsValidImageURL

    GO

    -----------------------------------------------------------------------

    use master

    GO

    create master key encryption by password = 'password';

    GO

    create asymmetric key SQLKey from file = 'D:\SQLKeyFile.snk';

    GO

    create login SQLLogin from asymmetric key SQLKey;

    GO

    GRANT EXTERNAL ACCESS ASSEMBLY to SQLLogin;

    GO

    CREATE USER SQLUser FOR LOGIN SQLLogin;

    GO

    USE TestCenter

    GO

    CREATE ASSEMBLY SQLCLR FROM 'D:\SQL.dll'

    GO

    CREATE FUNCTION IsValidImageURL

    (

    @paramURL as nvarchar(200)

    )

    RETURNS NVARCHAR(500)

    AS EXTERNAL NAME SQLCLR.[SQL.URLValidator].IsValidImageURL

    GO

    ----------------------------------------------------------------------------

    SELECT dbo.IsValidImageURL('http://forums.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif')

     

     

    Same Error: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

     

    First option is not allowing me to register assembly at all...

    Let me know what is missing in above two options....

     

    Thanks

    Bhavesh

    Wednesday, May 28, 2008 2:03 PM