solution to SQL Server stored procedure Access database permissions exception problem

Server|sqlserver| Stored Procedure | access | data | database | problem

Recently submitted a Web form with asp.net 2.0 + SQL Server, using C # to write a stored procedure to complete inserting records into a SQL Server database table. When this stored procedure is invoked, an exception to the storage permissions appears. The following details the process of the exception generation and the solution.

1. Operation Steps:
1 using asp.net 2.0, write a stored procedure in C # that operates on a table in database test with the following code:

public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Submit (String stranswer)
{
using (SqlConnection connection = new SqlConnection ("Data source=ws;initial catalog=test;integrated security=false; Trusted_connection=yes; "))
{
Connection.     Open (); An exception has been performed to this step, as described below * * *
String cmdtxt = "INSERT into dbo. Response_scl90 VALUES ("+ Stranswer +");
SqlCommand command = new SqlCommand (cmdtxt, connection);
SqlDataReader reader = command. ExecuteReader ();

}
}
}

2 compiles the Submit_answer.dll, then registers the DLL in SQL Server and creates the stored procedure, the SQL script is as follows:

CREATE ASSEMBLY Submit_answer
From ' d:\study\c#\ evaluation system \website1\storedprocedure\submit_answer\bin\debug\submit_answer.dll ';
Go

CREATE PROCEDURE dbo. Submit_answer
(
@strAnswer nvarchar (256)
)
With EXECUTE as OWNER
As
EXTERNAL NAME Submit_Answer.StoredProcedures.Submit
Go

3 Finally, call the stored procedure in. NET with the following code:
SqlConnection connection = new SqlConnection ("Data source=ws;initial catalog=test;integrated security=false; Trusted_connection=yes; ");

String cmdtxt = "dbo." Submit_answer ";
SqlCommand command = new SqlCommand (cmdtxt, connection);
Command.commandtype = CommandType.StoredProcedure;

Command. Parameters.Add ("@strAnswer", SqlDbType.NVarChar);
Command. parameters["@strAnswer"]. Value = stranswer;

Command. Connection.Open ();
SqlDataReader dr = command. ExecuteReader ();

Command. Connection.close ();

2. Description of the exception:
Connection on execution to stored procedure. Open (); a sentence, an exception, exception description and then stack information is as follows:
Exception Details: system.data.sqlclient.sqlexception:a. NET Framework error occurred during execution of user defined routine or AG Gregate ' Submit_answer ':
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.SqlClient.SqlConnection.PermissionDemand ()
At System.Data.SqlClient.SqlConnectionFactory.PermissionDemand (DbConnection outerconnection)
At System.Data.ProviderBase.DbConnectionClosed.OpenConnection (DbConnection outerconnection, dbconnectionfactory ConnectionFactory)
At System.Data.SqlClient.SqlConnection.Open ()
At Storedprocedures.submit (SqlChars stranswer)

3. A brief analysis:
It appears that there is no access to the database in the stored procedure, because there is an error when the database is connected to open, and you find some information, and you don't see the problem. Then he asked questions in the community.

4. Solution:
In the "Ms-sql Server Troubleshooter" section, zlp321002 (Tornado 2006) Answers this question, for details, see:
http://community.csdn.net/Expert/TopicView3.asp?id=4790457
Now collated and summarized as follows:
(Thanks to zlp321002 (Tornado 2006), the solution described below originates from zlp321002 (Tornado 2006))

1 Open the External Access option for the database (external_access_option)

Alter database name
SET trustworthy on

Reference: Description of the database external access option (External_access_option) (excerpt from SQL Server books Online)

Trustworthy {on | OFF}

On

Database modules (for example, user-defined functions or stored procedures) This use a impersonation context can access R Esources outside the database.

Off

The database modules in a impersonation context cannot access resources outside the database.

The trustworthy is set to off whenever the "database is attached."

By default, the master database has trustworthy set to on. The model and tempdb databases always have trustworthy set to off, and the value cannot is changed for these databases.

To set this option, requires membership in the sysadmin fixed server role.

The status of this option can is determined by examining the is_trustworthy_on column in the sys.databases catalog view.

2) Set the permission_set of the stored procedure DLL as External_access

In step 2nd of the Operation step, the original
CREATE ASSEMBLY Submit_answer
From ' d:\study\c#\ evaluation system \website1\storedprocedure\submit_answer\bin\debug\submit_answer.dll '
Go
To
CREATE ASSEMBLY Submit_answer
From ' d:\study\c#\ evaluation system \website1\storedprocedure\submit_answer\bin\debug\submit_answer.dll '
With permission_set = external_access
Go

Reference: About Permission_set settings in Create assembly (excerpt from SQL Server books Online)

Permission_set {SAFE | external_access | UNSAFE}
Specifies a set of code access permissions that are granted to the assembly when it was accessed by SQL Server. If not specified, the SAFE is applied as the default.

We are recommend using SAFE. The SAFE is the most restrictive permission set. Code executed by a assembly with SAFE permissions cannot access external system resources such as files, the network, env Ironment variables, or the registry.

External_access enables assemblies to ACCESS certain EXTERNAL system resources such as files, networks, environmental vari Ables, and the registry.

UNSAFE enables assemblies unrestricted access to resources, both within and outside of SQL Server. Code running from within a UNSAFE assembly can call unmanaged code.

Security Note:
SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without Sing outside an instance of SQL Server. We recommend using external_access for assemblies this ACCESS resources outside of SQL Server. EXTERNAL_ACCESS assemblies include the reliability and scalability protections of SAFE assemblies, but from Spective are similar to UNSAFE assemblies. This is because code in external_access assemblies runs by default under the SQL Server service account and accesses Exter NAL resources under this account, unless the code explicitly impersonates the caller. Therefore, permission to create external_access assemblies should is granted only to logins this are trusted to run code u NDEr the SQL Server service account. For more information about impersonation, the CLR integration security. Specifying UNSAFE enables the code in the Assembly complete freedom to perform operations in the SQL Server process spaces that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language. UNSAFE permissions should is granted only to highly trusted. The sysadmin fixed server role can create and alter UNSAFE assemblies.

After you finish the above changes, run the Web page again, submit the form, no longer appear abnormal.

5. Summary:

It seems this stored procedure access rights are resolved from the following two aspects: first open the external access options for the database, allowing the database module access to external resources; then set the permission_set of that stored procedure DLL, which is to set the access rights of this DLL to allow access to external resources. This allows the stored procedure to access the database.