Hi,
Is it possible, in a CLR Stored Procedure, to connect to a different
database on the same server as the database under which the current
connection is for/running?
In other words, within a CLR Stored Proc running on database A, is it
possible to then connect to Database B, and insert records into tables in
Database B from data contained in tables in Database A, where both Database
A
and Database B reside on the same instance of SQL Server 2005 (same server)?
Thanks for any help or ideas on this.Thank you for posting in the MSDN newsgroup.
As for SQL 2005 CLR stored procedure, we can establish other connection to
external database(even external database server instance/remote) in the CLR
sp's code. e.g.
=======================
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PriceSum(out SqlString value)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
//access the same database's resource...
}
// access an external database on the same server/same instance
using (SqlConnection conn = new SqlConnection("Data
Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT ProductCategoryID,
Name FROM Production.ProductCategory", conn);
SqlDataReader reader = comm.ExecuteReader();
using (reader)
{
while (reader.Read())
{
sb.Append(")(" + reader.GetString(1));
}
}
}
value = new SqlString(sb.ToString());
}
}
========================
And as for such CLR assembly, you need to grant it "External Access"
permission(safe is insufficient).
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
Thanks for that Steven! I granted my assembly "External Access" as opposed
to "Safe", and it compiled fine. However, when I attempted to deploy it to m
y
SQL Server, i got the following error message:
CREATE ASSEMBLY for assembly 'MyCLRStoredProcTest' failed because assembly '
MyCLRStoredProcTest ' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS
.
The assembly is authorized when either of the following is true: the
database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the databas
e
has the TRUSTWORTHY database property on; or the assembly is signed with a
certificate or an asymmetric key that has a corresponding login with EXTERNA
L
ACCESS ASSEMBLY permission.
I'm guessing for best security, I should sign my assembly with a certificate
or an asymmetric key. Would you be able to suggest where to look to find out
what is involved with that? (and the other 'fix' options noted in my error
message) Thanks.
Best Regards,
Mark
"Steven Cheng[MSFT]" wrote:
> Thank you for posting in the MSDN newsgroup.
> As for SQL 2005 CLR stored procedure, we can establish other connection to
> external database(even external database server instance/remote) in the CL
R
> sp's code. e.g.
> =======================
> public class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void PriceSum(out SqlString value)
> {
> StringBuilder sb = new StringBuilder();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> //access the same database's resource...
>
> }
>
> // access an external database on the same server/same instance
> using (SqlConnection conn = new SqlConnection("Data
> Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True")
)
> {
> conn.Open();
> SqlCommand comm = new SqlCommand("SELECT ProductCategoryID,
> Name FROM Production.ProductCategory", conn);
> SqlDataReader reader = comm.ExecuteReader();
> using (reader)
> {
> while (reader.Read())
> {
> sb.Append(")(" + reader.GetString(1));
> }
> }
> }
> value = new SqlString(sb.ToString());
> }
> }
> ========================
> And as for such CLR assembly, you need to grant it "External Access"
> permission(safe is insufficient).
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>|||Hello Mark,
The error message indicate that you need to turn on the "TRUSTWORTHY"
property of the database which will add an "external access" assembly.
Anyway, you can follow the below web article's steps which include the
complete things we need to configure when install a custom assembly which
will require External_Access:
#CLR Stored Procedure Calling External Web Service
http://davidhayden.com/blog/dave/ar...04/25/2924.aspx
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks again Steven! Works beautifully. And thanks for the link to the
blog...very helpful.|||You're welcome Mark,
Have a nice day!
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
