Hi folks, I am trying to run the following function (which works in Visual Studio just fine) but it appears SSRS can't access data in the same way as Visual Studio? There are extra permissions needed - can anyone point me in the right direction? (I am basically trying to emulate the DLookup function from Access for a migration we are planning.) Here's the error: --- begin Error output 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. 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 ReportExprHostImpl.CustomCodeProxy.DLookup(String dField, String dTable, String dCondition) The action that failed was: Demand The type of the first permission that failed was: System.Data.SqlClient.SqlClientPermission The Zone of the assembly that failed was: MyComputer --- end Error output Here's the code: ---begin code snippet Public Function DLookup(ByVal dField As String, ByVal dTable As String, ByVal dCondition As String) As String Dim myConnection As New System.Data.SqlClient.SqlConnection("Data Source=SHARE-CB;Initial Catalog=testCascades") Dim dr As System.Data.SqlClient.SqlDataReader Try myConnection.Open() 'opening the connection Dim myCommand As New System.Data.SqlClient.SqlCommand("SELECT TOP 1 " & dField & " FROM " & dTable & " WHERE " & dCondition, myConnection) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read() 'reading from the datareader Return dr(0).ToString() 'displaying the data from the table End While dr.Close() myConnection.Close() Catch e As Exception Return e.ToString() End Try End Function --- end code snippet I've also tried adding : Dim pSql As New System.Data.SqlClient.SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted) pSql.Assert() but this causes other errors to appear - I want to be sure this step will help before troubleshooting it. Also, I tried turning CAS enforcement off, (caspol -security off) but I couldn't load/create projects after doing this. The following assembly is being referenced: System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Any suggestions are appreciated. Many thanks in advance!

Hi Sharebliss, You could add a linked server pointing to Access and use SELECT statement or stored procedure to get data from a linked server. After Access migration you need to point to SQL Server database in place of linked server.Sergei

Hi Sharebliss, You should add the following code to the function, and configure code security for the code assembly to solve the issue: Dim pSql As System.Data.SqlClient.SqlClientPermission = New SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted) pSql.Assert() Below is a article details the steps about granting permissions for custom code in SQL Server Reporting Services: http://support.microsoft.com/kb/842419 If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT

Hi Sergei and Jin - thanks for your answers. When I re-add the fragment you suggested Jin, I get a 'Type SQLClientPermission is not defined' error. What reference do I have to add to get this going? System.Data and System.Security are already in place. Thanks again guys, SB

Hi again guys, I also noticed that most articles discussing these permissions refer to having a custom assembly - does having custom code in SSRS qualify? I would think this refers to some external piece that has code being called by SSRS, not the actual code in SSRS itself, correct? Thanks again guys, SB

Hi Sharebliss, >>SELECT TOP 1 " & dField & " FROM " & dTable & " WHERE " & dCondition, myConnection This piece of code above is for .NET connecting to SQL Server database and to emulate DLOOKUP function in SQL Server. For SSRS create stored procedure and pass parameters @dField (being column name) and @dCondition (being condition) Within stroed procedure you use sp_executesql or exec to return data from SQL Server. In this case you don't need custom assembly at all use sp_excutesql with the above parameters (not preferable due to sql server injection) directly in the report data source. However, I don't see a use for DLOOKUP function in SSRS. All could be done in stored procedure just passing parameters.Sergei

Hi Sharebliss, >>SELECT TOP 1 " & dField & " FROM " & dTable & " WHERE " & dCondition, myConnection This piece of code above is for .NET connecting to SQL Server database and to emulate DLOOKUP function in SQL Server. For SSRS create stored procedure and pass parameters @dField (being column name) and @dCondition (being condition) Within stroed procedure you use sp_executesql or exec to return data from SQL Server. In this case you don't need custom assembly at all use sp_excutesql with the above parameters (not preferable due to sql server injection) directly in the report data source. However, I don't see a use for DLOOKUP function in SSRS. All could be done in stored procedure just passing parameters.Sergei

Hi Sergei, Forgive me if this is a strange question, as I'm still brand new to SSRS - does SSRS have it's own stored procedures apart from those on SQL Server? So you are suggesting I create a local SSRS stored procedure that uses sp_executesql to grab data from the server? The reason I interpreted it like this is from what I've learned so far, SSRS is reluctant to grant access to anything outside of itself and the report's dataset. Thanks Sergei, SB

Hi Sharebliss, I am trying to help and simplify what you try to achieve. SSRS has two modes 1. managed reports 2. user reports(based on published model) Managed reports: SSRS can run reports interactively (using parameters and drill-down functionality) and on scheduled basis without any user intervention. What you need is to create a data source based on t-sql/view/stored procedure, add parameters to filter data, design a layout and deploy a report. User reports: Build and publish report model (based on multiple database tables/views) A user can build its own reports based on that model and publish them on the report server. The answer to your question ("does SSRS have its own stored procedures apart from those on SQL Server?") is yes. SSRS has its own built-in functions. You could have your custom code and .NET assemblies. But I don't see any need for this particular function that emulates Access DLOOKUP. Just create your own stored procedure, deploy on SQL Server side and use it as a data source in reporting services. >>SSRS is reluctant to grant access to anything outside of itself and the report's SSRS runs under a windows account and therefore this account should have access to your database. Hope I answered all your questions. Sergei

Hi Sharebliss, I am trying to help and simplify what you try to achieve. SSRS has two modes 1. managed reports 2. user reports(based on published model) Managed reports: SSRS can run reports interactively (using parameters and drill-down functionality) and on scheduled basis without any user intervention. What you need is to create a data source based on t-sql/view/stored procedure, add parameters to filter data, design a layout and deploy a report. User reports: Build and publish report model (based on multiple database tables/views) A user can build its own reports based on that model and publish them on the report server. The answer to your question ("does SSRS have its own stored procedures apart from those on SQL Server?") is yes. SSRS has its own built-in functions. You could have your custom code and .NET assemblies. But I don't see any need for this particular function that emulates Access DLOOKUP. Just create your own stored procedure, deploy on SQL Server side and use it as a data source in reporting services. >>SSRS is reluctant to grant access to anything outside of itself and the report's SSRS runs under a windows account and therefore this account should have access to your database. Hope I answered all your questions. Sergei

Hi Sharebliss, I think I understand now that you are going to migrate Access reports to SSRS and a source of data is Access database. You don't want to make any changes to design of Access reports. There are many text boxes in Access reports bound to DLookup function. The only way to emulate Dlookup function is to use .NET assembly but it means multiple calls to Access database. Therefore, the performance might not be good. Also, there could be some caviats, for example SSRS Does not return new data from when MS Access data entry form still open. I would recommend to upsize Access databases to SQL Server 2008 R2 using SSMA for Access - SQL Server Migration Assistant 2008 for Access V4.0. But it means you have to re-design hundreds of Access reports.Sergei

Hi again Sergei, "But I don't see any need for this particular function that emulates Access DLOOKUP. Just create your own stored procedure, deploy on SQL Server side and use it as a data source in reporting services." There's where we find the catch, though - I won't be able to use a SQL Servier-side stored procedure as the data source. Remember, I'm doing a migration here and the reports I'm bringing in from Access use the DLookup function. This is why we have to emulate it. We have to do the migration for hundreds of databases (and hundreds of reports in each database), so there won't be scope to define custom stored procedures to serve as recordsets for each report in each database, right? It's a limitation based on the size of the migration. If you have any ideas how I could emulate DLookup in this scenario, without modifying the data source/report dataset, I'm all ears! SB

Hi again Sergei, What if I upsize the database, meaning the tables are now in SQL Server, but I have to keep the DLookup functions of the existing Access reports? Is my only option .NET Assembly, or do I have another option in that case to emulate DLookup? Thanks again Sergei, SB

Hi Sharebliss, What if I upsize the database, meaning the tables are now in SQL Server, but I have to keep the DLookup functions of the existing Access reports? Is my only option .NET Assembly, or do I have another option in that case to emulate DLookup? To emulate DLLOKUP you could use custom code or custom assembly - see Integrating .NET Code and SQL Server Reporting Services. See code for function GetCustomerOrderCount. You could write similar function and name it DLOOKUP. Also, you could use dataset (from a data source) to grab data from a database and use custom code to get values from the dataset - see Dataset in Custom Code in SQL Server Reporting ServicesSergei