Post reply

SQL-CLR stored procedure - is it the best solution for me?

  • December 1, 2010 at 9:06 am

    #388346

    One of our developers asked me to create a stored procedure for his smart-phone application which validates a User login. He wants to hand the stored procedure his UserName and Password and have the stored procedure return a 1 if the Username and Password match, or a 0 (zero) otherwise.

    The password information is stored in a "users" table, but it is encrypted using a SHA512 algorithm.

    I'm assuming that this encryption/decryption stuff can only be done via compiled code. So I wrote a small, SQL-CLR program to create a stored procedure which accepts two parameters and will return either a 1 or a 0.

    I enabled CLR on the database, created the assembly and then created the stored procedure using the following T-SQL code.

    CREATE PROCEDURE UserLogin

    (

    @UsernameNVARCHAR(20),

    @PwdNVARCHAR(20)

    )

    --RETURNS INT

    AS EXTERNALNAME mySQL_procs.StoredProcedures.UserLogin;

    GO

    Everything to this point compiled or ran cleanly. However, when I tried to execute it, I received the following error message:

    Msg 6522, Level 16, State 1, Procedure UserLogin, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "UserLogin":

    System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

    at System.Security.PermissionSet.Demand()

    at System.Data.Common.DbConnectionOptions.DemandPermission()

    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

    at System.Data.SqlClient.SqlConnection.Open()

    at StoredProcedures.UserLogin(SqlString Username, SqlString pwd)

    I next found an article http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx describing how to sign an assembly. I followed these steps as well, but am continuing to receive the same error message indicating that it is failing the security check.

    Can anyone point me toward further information that can help me to get this working?

    I'd sure appreciate any assistance you might be able to toss my way! Thanks!

    Larry

  • December 17, 2010 at 4:07 am

    This was removed by the editor as SPAM

  • March 29, 2011 at 2:38 pm

    #1305150

    Can you post your CLR code? from the error messages it sort of looks like it is trying to connect to a database server. That would not work unless the assembly had external access or unsafe settings.

    If all you need to do is decrypt or generate a hash of something there should be no need to connect to a database to do it. I'm confused about what you are trying to accomplish.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply