September 4, 2009 at 7:42 pm
I am trying to create a stored procedure using .NET in SQL 2005 (SQLCLR) to refer the customer table in a DSN (local) Database in another server.
The following is the error:
Msg 6522, Level 16, State 1, Procedure uspGetCustomerInformation, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "uspGetCustomerInformation":
System.Security.SecurityException: Request for the permission of type 'System.Data.Odbc.OdbcPermission, 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.Odbc.OdbcConnection.PermissionDemand()
at System.Data.Odbc.OdbcConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at uspCustomerInfo.StoredProcedures.uspGetCustomerInformation(String sCode)
.
I have set the DATABASE TRUSWORTHY level ON and running this query in SA mode while testing.
I dunno how to set this permission exactly. If any one could share a piece of working code, i would be much grateful.
Thank you
September 4, 2009 at 9:21 pm
Questions:
1) What permission set is your assembly installed with?
2) Why are you using ODBC instead of ADO.NET?
3) Why can't you use a linked server for this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 4, 2009 at 11:38 pm
Hi
Questions:
1) What permission set is your assembly installed with?
I used EXTERNAL permission in the project. VS 2008
2) Why are you using ODBC instead of ADO.NET?
The reason is I am very new to this area. also the enternal database is Microsoft Dynamics NAV native DB which has a ODBC driver installed on the local machine.
3) Why can't you use a linked server for this?
Dunno much about this.
Thank you
September 5, 2009 at 12:22 am
A better couple of questions would be.... What does the CLR do and why do you believe you need a CLR to do it?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2009 at 12:35 am
Okey, great.
This is the scenario. I need to pull only selected information from a NAV Customer table. So, I bult a stored procedure in CLR to accept a customer code and connect to NAV Native DB through ODBC and pull the [Name], [Address], [otherfields] and pass to user as a table.
My CLR code is as below
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data.Odbc.OdbcPermission
Imports System.Data.Odbc
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
_
Public Shared Sub uspGetCustomerInformation(ByVal sCode As String)
Dim cn As New OdbcConnection("Data Source=NAV_DB")
cn.Open()
Dim cmd As New OdbcCommand("SELECT [No_],[Name],[Address 1].[Address 2],[Address 3] FROM Customer WHERE [No_] = '" & sCode & "'", cn)
cmd.CommandType = CommandType.Text
SqlContext.Pipe.Send(cmd.ExecuteReader(CommandBehavior.CloseConnection).ToString())
cmd = Nothing
cn.Close()
End Sub
End Class
My idea is to run the SP like this
EXECUTE [Database].[dbo].[uspGetCustomerInformation] 'CUST001'
Hope this helps to understand the entire process i expected.
Thank you again
September 5, 2009 at 5:34 pm
meelan (9/4/2009)
1) What permission set is your assembly installed with?I used EXTERNAL permission in the project. VS 2008
You may need to use the UNSAFE permission set.
2) Why are you using ODBC instead of ADO.NET?
The reason is I am very new to this area. also the enternal database is Microsoft Dynamics NAV native DB which has a ODBC driver installed on the local machine.
See if you can get it to work with ADO.NET. If you can you may be able to keep it at EXTERNAL_ACCESS.
3) Why can't you use a linked server for this?
Dunno much about this.
Its OK, you probably cannot do it to an Navison DB anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2009 at 7:55 pm
meelan (9/5/2009)
Okey, great.This is the scenario. I need to pull only selected information from a NAV Customer table. So, I bult a stored procedure in CLR to accept a customer code and connect to NAV Native DB through ODBC and pull the [Name], [Address], [otherfields] and pass to user as a table.
If that's the case, why not just setup an ODBC (or, maybe, an OLEDB) provider on the server and create a linked server to the remote data?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 6, 2009 at 5:09 am
There's nothing special about a Navision database (except maybe in the 'special needs' sense) so a linked server would be by far the better option. Trying to use .NET code to access a remote DSN to read a table is perverse.
Linked servers are very easy to set up and work with (NAV uses a standard SQL Server database, in case that's not clear) - see the following link to a Books Online entry as a good starting point:
September 6, 2009 at 7:35 am
Thank you very much for your valuable comments and help.
BY using the guidelines provided here and little bit of syntax correction (and small modification in NAV database), the code now works. But, it returns a MESSAGE which contains "System.Data.Odbc.OdbcDataReader" only.
Anyway, I will learn more in this area while looking at the linked server option also.
Tks again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply