Sql Customizing Report: access

Showing posts with label access. Show all posts

Tuesday, March 27, 2012

Customize the reporting services interface display reports.

Hello:
We are using the reporting services interface to display reports. By using
the security we restricted some access and everything works fine.
Now we would like to change the way the parameters prompt works.
Is there any way of applying styles to the pages rendered by the reporting
server?
Thanks,
Daniel Bello.You can modify the RSReportServer.config file to specify a custom style
sheet for HTML Viewer. The <HTMLViewerStyleSheet> setting is not included in
the file by default. You must type it into the <Configuration> selection of
the RSReportServer.config file and then specify the style sheet you want to
use. Do not include the .css file extension when specifying the style sheet.
The following example provides an illustration of how to specify the style
sheet:
<Configuration>
...
<HTMLViewerStyleSheet>MyStyleSheet</HTMLViewerStyleSheet>
...Read the full article at:
http://msdn2.microsoft.com/en-us/library/ms345247.aspxThanks,Daniel Bello
Urizarri.

Thursday, March 22, 2012

Custom Task: How to access/modify the variables of a package

Hi,

I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.

Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.

This is the code I had in the Script Task:

Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False

While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()

If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success

All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.

Any thoughts? I'd really appreciate some help!

P.S. I've been working on this for 2 days!

I don't have an answer to your question, but have you looked at using package configurations (SSIS > Package Configurations from the BIDS menu)? these can be stored as simple XML files that can be used for the same purpose as what I think you are trying to do here.

Note that BIDS will not save connection string passwords for you - you would have to open the XML file and edit it manually to add these if you are using it for connection strings.

You can use these XML files to store variable values as well as lots of other properties of your package objects. You could write your own code to manipulate the contents of the XML configuration files however you like.

Another option would be to store your values in a database and fetch the values into the variables using SSIS Execute SQL tasks or a similar method.

Maybe you've thought of all this already and ruled it out for some other reason - but based on your post this would seem a much simpler method?

|||Hi Slicktop,

Yes, I tried the XML config file but you cannot have configurations that don't exists in the package. About the SSIS_Configuration table, I thought of this but it's not a good idea for my deployment process. Somehow, I need to get the information from this table and put it in a flat file so I can store it in CVS. Plus, I need to fill this table every time I deploy my packages. I know this is not so difficult but my client don't want to do this.

There is a property in the Package object which disable the warning messages from the configuration file. If I set to True, I don't get an error message when I have just one XML config file with multiple configurations which they may or may not exists in the package to be executed. I don't like doing this because it doesn't seem right.

Thanks for your ideas.|||

What about just reading the flat file using SSIS? You could either load it into a work table and then query it to set variables, or maybe even use a transform to extract the values you need.

When you say "you cannot have configurations that don't exist in the package", what do you mean exactly? Your variable names aren't defined in the package?

|||That's right!. I don't have the same variables in every package. I have a log file per package so in the flat file I have:

log_package_1 = something
log_package_2 = something
log_package_3 = something
conn_db = connection string to db
file_vpn = path to the vpn file

and so on, so in package 1 I only have declared variable log_package_1 but not the rest of the variables of my flat file. Reading the flat file with SSIS could be a possible option but the thing is I have a custom flat file, I mean it's not comma separated and I have some "tags" so I don't think SSIS can read it.

Thanks for the ideas!|||

well obviously I don't know all the details of your situation but it sure seems like you are trying to do this the hard way.

Is there some reason why your custom flat file has to have a "custom" format?

If your goal is to dynamically populate package variables, and the variable names are defined in the package definition, then you can use package configurations to do this. If your goal is to have only a single flat file that contains all variable values across multiple packages, then why not just write these into a standard, machinable format (XML, delimited file, whatever) and then use SSIS to load the file into a DB table. Then from each package, query the table for the variable values needed for that particular package and use the resultset to populate the package variables.

Your table could be generic, with two varchar columns, var_name and var_value.

so, in package 1, you could write an execute SQL task that runs "select var_value from control_table where var_name = 'log_package_1', etc.

If you put a bit of thought into this you could probably make parts of this solution quite generic so that you could write it once and copy/paste the tasks into each package or nest your packages to re-use it. for example, maybe add a "package_name" column to the control table that you could use to query all variables for a package using the same SQL.

sorry, I know I haven't answered your original question (don't know the answer) but thought maybe there is a better (easier) way to do this.

|||Why are you punishing yourself with such a masochistic task?

I mean, why not just use XML Configuration files?|||

Has the original question been answered?

The Task.Execute method is where you will place the code that reads from the file and sets variables. The execute gives you access to the VariableDispenser object as on of the parameters, in much the same way as the TaskUI.Initialize method does, is this not enough?

The Dts.Variables syntax you have used in the Script Task is not applicable, you need to manually control the locking in the variable dispenser. Look at the Lock* methods as documented for the variable dispenser.

|||Hi DarrenSQLIS,

No, the original question haven't been answered yet. I know that Dts.Variables is not applicable in my case when creating a Custom Task. I checked the VariableDispenser object and there are a few methods. For example, the GetVariables(ref Variables variables) but I don't have the collections of variables to pass it as the parameter for this method.

I think I need somehow to tell SSIS which variables are for read-only access and which ones are for read/write. Even so, I would like to know first the variable name and their respective values first. The VariableDispenser object seems useless because I don't know how to get the variables within the package that is executing my Custom Task.

My goal is to know the name of all the User Variables in a package and then set these variables to their respective values that I'm reading from a flat file.

Using XML configuration files is not a solution, I'm not going to set the SuppressConfigurationWarnings property to True in each package. Probably you think I'm trying to do it the hard way but I have to follow some constraints and standards from my client.

Thank you all for your replies.|||

The methods, GetVariables, LockOnForRead or LockOneForWrite all expect a reference parameter of type Variables. This is just the variables collection that is filled with the 'locked' variables that you have asked for, e.g.

Variables variables = null;
variableDispenser.LockOneForRead(this.SnapshotName, ref variables);
snapshotName = variables[0].Value as string;
variables.Unlock();

From what you're saying though you don't know in advance what variables you want to lock, and that is a problem. There is no way to just ask for a list of all variables. I assume your pseudo config item and the variable name match, so at best you can loop through the config items and ask if a variable of that name exists, using the VariableDispenser.Contains method. If found then lock it and do what you want, but you cannot just eneumerate a collection of all variables in the package to find out what is there.

|||Hi DarrenSQLIS,

Yeah, you're right! My pseudo config item and the variable name match so I can loop through the config items and that's what I did. I wasn't understanding the purpose of the GetVariables() method of VariablesDispenser. After I played with it some time I realized it copies the variables from the package that you lock (read-only or r/w) before to the Variables collection that you pass by reference. Then you can access the variables from this collection and set them. What I don't understand completely is how the variables are return to the package or VariablesDispenser. I'm guessing is after you unlock them.

Well, thank you so much for your tips/ideas/suggestions. I finally finished building my Custom Task as I wanted. It's a shame you cannot access all the variables without knowing the name of the variables ahead. In this case I'm assuming that whatever is in the package is also in the config file but my process will fail if the package has a variable that the config file doesn't.sql

Tuesday, March 20, 2012

Custom server permissions?

Hi,
I need to grant a user access to one or two specific system stored
procs without giving permission to everything else in the fixed server
role that allows them. Specifically, they need rights to
sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
all the rights associated with securityadmin. Is there a way to grant
specific rights to just these?
TIA,
BarryBoth of these system stored procedures have hard coded permission checks in
them. I'm not a big fan of these as it really limits flexibility. The only
way would be to alter them to remove these checks but this would not be a
supported scenario. sp_addlinkedserver has a hard coded check for membership
of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
check for membership of the securityadmin server role. The only alternative
would be one that I use a lot for scenarios where I want lower privilege
users to be able to do a very specific action and that is to write a queue
system whereby they basically have a table that they can insert rows into
via a stored procedure and this table is polled by a SQL Agent job that runs
once a minute and executes the specific commands they are allowed to run.
This way you can code so they can only do a very specific action (otherwise
you would lead yourself open a large security hole)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<barrygilbertusa_no_spam@.yahoo.com> wrote in message
news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to grant a user access to one or two specific system stored
> procs without giving permission to everything else in the fixed server
> role that allows them. Specifically, they need rights to
> sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
> all the rights associated with securityadmin. Is there a way to grant
> specific rights to just these?
> TIA,
> Barry
>|||Jasper,
Thanks for your reply. I'll give this a try.
Barry
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#hwMr6n0EHA.1392@.TK2MSFTNG
P14.phx.gbl>...[vbcol=seagreen]
> Both of these system stored procedures have hard coded permission checks i
n
> them. I'm not a big fan of these as it really limits flexibility. The only
> way would be to alter them to remove these checks but this would not be a
> supported scenario. sp_addlinkedserver has a hard coded check for membersh
ip
> of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
> check for membership of the securityadmin server role. The only alternativ
e
> would be one that I use a lot for scenarios where I want lower privilege
> users to be able to do a very specific action and that is to write a queue
> system whereby they basically have a table that they can insert rows into
> via a stored procedure and this table is polled by a SQL Agent job that ru
ns
> once a minute and executes the specific commands they are allowed to run.
> This way you can code so they can only do a very specific action (otherwis
e
> you would lead yourself open a large security hole)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> <barrygilbertusa_no_spam@.yahoo.com> wrote in message
> news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...

Monday, March 19, 2012

Custom Report Item and embedded images

Hi,

I would like to integrate an embedded image into a custom report item. However, I can't find a property which gives access to the embedded images collection. What would be the right approach to load an embedded image within the process method?

Kind regards

Benno

Found a workaround - I will store images as own custom properties.|||

Hello,

Could you tell me how to create a custom report item that modifys the RDL file.

Thanks!!

SQLNew

Sunday, March 11, 2012

Custom protection of rows (problem with updatability of viwes with "WITH VIEW_METADATA" an

My question is connected with tip "Using Views to Control User Access to
Data":
http://www.microsoft.com/sql/techinfo/tips/administration/controlledaccess.asp
I need to update (using Access 2000/2002) table defended in such way,
but it cause some additional problems.
1. I can't update this view when user haven't SELECT permissions
on the base table (user have only permissions to the view, because I try to
defend base table).
As I understand ADO tries directly update base table and fails.
2. When I add "WITH VIEW_METADATA" to enforce ADO to update
view, not base tables I have another problem - I need to add
prymary key columns from "authtable" to make view updateble
for ADO. In such way I also had to add some additional procedures
to dataform in Access to fill this additional columns automatically (for
insert operation).
It helps but it cause additional procedures at client side.
3. The best way to resolve this problem only on server side as I thought
was to create view like this (using "WITH VIEW_METADATA" and "IN" clauses):
CREATE VIEW v_data
WITH VIEW_METADATA
AS
SELECT <column_list>
FROM dbo.mytable AS a
WHERE a.Pkey
IN
(
SELECT b.DataKey
FROM dbo.authtable AS b
WHERE b.userid = suser_sname()
)
But this view is not updatable in Access 2000/2002
because ADO DOES NOT SEE PRIMARY KEY INFORMATION
when there is combination "WITH VIEW_METADATA" and "IN" clauses.
I cann't use this view without "WITH VIEW_METADATA", in this case
view is "updatable" for ADO, but updates fails as described in p.1.
I send this bug year ago to MS when was mdac 2.7.
Now we have MDAC 2.8, SQL SP 3, Yukon Beta - ADO has the same problem with
updates
when there is combination "WITH VIEW_METADATA" and "IN" clauses
I think, it helps to make simple customised rowbased security system not
only for SELECT
but also for INSERT, UPDATE and DELETE in combination "SQL Server - MS
Access" or other ADO based clients.Hello Max:
You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
ii> 1. I can't update this view when user haven't SELECT permissions
ii> on the base table (user have only permissions to the view, because I
ii> try to defend base table).
ii> As I understand ADO tries directly update base table and fails.
In Access 2002, open the view in design mode, open properties, select
"Update using view rules".
ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com|||"Vadim Rapp" <vrapp@.nospam.polyscience.com> ñîîáùèë/ñîîáùèëà â íîâîñòÿõ
ñëåäóþùåå: news:e1IXvQULEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello Max:
> You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
> ii> 1. I can't update this view when user haven't SELECT permissions
> ii> on the base table (user have only permissions to the view, because I
> ii> try to defend base table).
> ii> As I understand ADO tries directly update base table and fails.
> In Access 2002, open the view in design mode, open properties, select
> "Update using view rules".
> ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
> Vadim
> ---
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>
Hello Vadim,
Thanks for reply,
Chekbox "Update using view rules" exactly adds "WITH VIEW_METADATA" clause
to view definition and this situation adds aditional problems as described
in p.2. But when I want add more sophisticated rules for user selection
permissions and make it updatable in Access I get very processor time
cosuming solutions. Execution plans for view scheme discribed in p.3 for my
tasks and needed rules some times more than 10 time quick than the same that
Access (ADO) understands as updatable - this the main problem...
And root of problem is ADO uncorrect understanding of combination of clauses
"WITH VIEW_METADATA" and "IN" (also "EXISTS").
Ó Ëüâîâ³ êëàñíî, ïðàâäà ùå òðîõè çèìíî áóâàº, ³ ÷àñ â³ä ÷àñó äîùèòü. Â
íåä³ëþ 2 òðàâíÿ áóäåìî ñâÿòêóâàòè äåíü ì³ñòà.
MAX|||Hello Max:
You wrote in conference microsoft.public.sqlserver.server on Thu, 29 Apr
2004 14:05:18 +0300:
II> And root of problem is ADO uncorrect understanding of combination of
II> clauses "WITH VIEW_METADATA" and "IN" (also "EXISTS").
It looks like it's not ADO but Access. I see that Access indeed does not
allow to add records to such a view; however, I successfully executed the
following pure ado code in VB:
rs.CursorLocation = adUseClient
rs.Open "view1", conn, adOpenKeyset, adLockBatchOptimistic
rs.AddNew
rs!c1 = "a"
rs!c2 = "b"
rs!id = 14
rs.UpdateBatch
rs.Close
where view1 was
ALTER VIEW dbo.View1
WITH VIEW_METADATA
AS
SELECT dbo.t1.c1, dbo.t1.c2, dbo.t1.id, dbo.t1.auth
FROM dbo.t1 INNER JOIN
dbo.authtable ON dbo.t1.auth >= dbo.authtable.minvalue
WHERE (dbo.authtable.userid = USER_NAME())
Note that I did not include in the view anything from authtable.
Another interesting possibility is described in BOL in "Create View"
article, which says: "INSTEAD OF triggers can be created on a view in order
to make a view updatable".
As a side note: you might receive more advise if you asked the question in
more relevant newsgroups. This one is mainly read by server administrators;
you might try .access.adp.sqlserver and .data.ado (though, as I said, it
looks like ADO is innocent here).
Êðiì òîãî, êîëè òè ïèøåø â àíãëiéñüêó ãðóïïó, òî ïèøè ñâîº iì'ÿ
ïî-àíãë³éñüêè.
regards,
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

Thursday, March 8, 2012

Custom Function in SQL Statement?

In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.

I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".

What do I need to do to make this work?
All help is greatly appreciated!

Lynnetteasp.net and sql server are two different things...you cannot call a vb/c# function in your sql statement...you can write the function in a UDF in your sql server and call it in your sql statement.|||Thanks for your response. I am brand new to SQL Server, but not so new to asp.net. Any ideas how to modify this vb.net function to a UDF?

Thanks again for all your help!
lds

 Function GetWorkStatus(ByVal sU As Object, ByVal sSG As Object) As Integer
 Dim dtEdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim mydateMonth As Integer = DatePart("m", dtEdate)
 Dim thisDay As String = CStr(DatePart("d", dtEdate))
 Dim sDayOfWeek As String = CStr(DatePart("w", dtEdate))
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim cmd As New SqlCommand("usp_CheckWorkStatus", cnn)
 Try
 With cmd
 .CommandType = CommandType.StoredProcedure
 With .Parameters.Add("@.ForThisDate", SqlDbType.DateTime)
 .Value = dtEdate
 End With
 With .Parameters.Add("@.mydateMonth", SqlDbType.Int)
 .Value = mydateMonth
 End With
 With .Parameters.Add("@.thisDay", SqlDbType.VarChar, 2)
 .Value = thisDay
 End With
 With .Parameters.Add("@.theUnion", SqlDbType.NVarChar, 2)
 .Value = sU
 'Session("sUn") = sU
 End With
 With .Parameters.Add("@.theSG", SqlDbType.NVarChar, 50)
 .Value = sSG
 'Session("strSG") = sSG
 End With
 End With
 cnn.Open()
 Dim da As New SqlDataAdapter(cmd)
 Dim ds As New DataSet
 da.Fill(ds, "SGDetails")
 'no record in FLSAScheduleGroup
 If ds.Tables("SGDetails").Rows.Count = 0 Then
 Select Case sSG
 Case "08"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 7 Then 'Sunday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "09"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 2 Then 'Sunday or Monday
 Return 0
 Else
 Return 1
 End If
 Case "12"
 If sDayOfWeek = 4 OrElse sDayOfWeek = 5 Then 'Wed or Thurs
 Return 0
 Else
 Return 1
 End If
 Case "13"
 If sDayOfWeek = 5 OrElse sDayOfWeek = 6 Then 'Thurs or Friday
 Return 0
 Else
 Return 1
 End If
 Case "14"
 If sDayOfWeek = 6 OrElse sDayOfWeek = 7 Then 'Friday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "31"
 If sDayOfWeek = 1 Or 7 Then
 Return 0
 Else
 Return 1
 End If
 Case Else
 Return 1
 End Select
 ElseIf ds.Tables("SGDetails").Rows.Count = 1 Then 'if there is a row in FLSAScheduleGroup
 Return 0
 End If
 Catch ex As Exception
 lblError.Text = ex.ToString
 Finally
 With cmd.Connection
 If .State = ConnectionState.Open Then
 .Close()
 End If
 End With
 Session("sUn") = Nothing
 Session("strSG") = Nothing
 End Try
 End Function

|||what you have seems to be fine although it can be fine-tuned a little bit more..i dont see where you are calling a function in a sql stmt here..|||I wrote the function for the vb.net code behind, and called it from the <ItemTemplate Text=> part of the datagrid.

I have no idea how to make it work in SQL - don't know how to write a UDF.

Thanks again for all your help. Sorry the code is so spaced out - don't know what the "POST-ing" did to it.

Lynnette|||ok you got me confused when you said you are trying to call a function in codebehind from SQL.

what you have seems to be a regular vb function that queries a database to fill a dataset. so what are you trying to do now ? does this function not work ? do you get any errors and if so at which line ? and what is the xact error message ?|||Thanks so much for sticking with me here. I'm the only one at my office that does .NET, so I'm kind of an island here.

The vb.net Function "GetWorkStatus" in the post above works fine. I get the error, "not a recognized function name" when I try to call it from another function, that is using it in the SQL statement to identify values for the specified field. Here is the calling function:


 Private Sub GetTmpRecs()
 'uses the AppSettings table to generate WHERE clause from the filter values Dim dtWdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim wValue As String = Session("svFilterValue")
 Dim dtEdate As DateTime = Now()
 Dim strUser As String = Session("sUser")
 Dim sSQL As String = "INSERT INTO tmpFLSAEmpInfo "
 sSQL = sSQL & " (Emp_Number, PT_ID, Name_full, Division, Department, Job_Dept_Code, Dept_Mgr, "
 sSQL = sSQL & " DeptInfo, Job_Supervisor, Location, Shift, sUnion, sSG, WrkDate, WrkStatus, HrsWorked, Username, AddDate) SELECT "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Emp_Number, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[ID], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Name_Full, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Division, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Department,"
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Job_Dept_Code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Dept_Mgr, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Department + ' - ' + hcsosharedtables.dbo.employee2.Dept_Mgr + ' - ' + hcsosharedtables.dbo.employee2.job_dept_code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Job_Supervisor, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Loc_Name, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Shift, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[Union], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Sched_Group, "
 sSQL = sSQL & "'" & dtWdate & "', "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS WrkStatus, "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS HrsWorked, "
 sSQL = sSQL & "'" & strUser & "', "
 sSQL = sSQL & "'" & dtEdate & "' "
 'sSQL = sSQL & "INTO tmpFLSAEmpInfo "
 sSQL = sSQL & "FROM hcsoSharedTables.dbo.Employee2 " & wValue & ""
 Dim ds As DataSet = New DataSet
 constants.ExecuteSQL(sSQL, constants.SQLConStrFLSA)
 End Sub

|||So I got it right..you were indeed trying to call a vb function into an sql query..I can suggest moving the entire code into a stored proc..move all the logit into it..you can get it all done in one trip.

Wednesday, March 7, 2012

custom error page

Hello,
Is there a a way to catch general errors on ssrs and display a custom error page to the user?

The goal is that if a user try to access a report when the ssrs is down, He won't see the RS general error but a custom notice from me.

Thanks.

If you are using report manager (which is an ASP.NET web application), I guess you can do that by configuring your custom error page in web.config file available in Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager folder. Set mode="on" for customErrors tag and define the custom error page as you do for any other ASP.NET web application.

Shyam

|||

More details on how to use custom error pages in a web application:

You can use defaultRedirect attribute of customErrors tag to tell the application to go to another custom web page by default if any error occurs. Also you can define error pages for specific types of errors like 401, 402 etc. and add them as error tags inside this customErrors tag. En example would look like this:

where error.html, accessdenied.html and pagenotfound.html are the files that you create and place in the appropriate folder on report manager virtual directory.

Shyam

|||Hi Sundar,

Thats exctly what I did after I read your previus post

Hope it will work fine. I now have to do some tasting.

Thanks for the solution!

custom error page

Hello,
Is there a a way to catch general errors on ssrs and display a custom
error page to the user?
The goal is that if a user try to access a report when the ssrs is
down, He won't see the RS general error but a custom notice from me.
Thanks.Absolutely possible as we have it implemented.
Absolutely no idea how it was achieved as I'm not a developer :(
Sorry
"nicknack" <roezohar@.gmail.com> wrote in message
news:1174999816.602638.279850@.y80g2000hsf.googlegroups.com...
> Hello,
> Is there a a way to catch general errors on ssrs and display a custom
> error page to the user?
> The goal is that if a user try to access a report when the ssrs is
> down, He won't see the RS general error but a custom notice from me.
> Thanks.
>

Custom Desktop Application

Hi All,

Today we are very pleased to announce the CTP release of Microsoft SQL Server 2005 Everywhere Edition Access Database Synchronizer (ADS) for which many of you been waiting eagerly. To download the product you can visit the SQL Server Mobile Edition/SQL Server Everywhere Edition product page or click on this link to Access Database Synchronizer reach the download page directly.

Access Database Synchronizer provides a way to synchronize data between Microsoft Access database on a desktop and Microsoft SQL Server 2005 Everywhere Edition database on a device.

For more details you can visit our blog - http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx

I urge you all to install and try out the solution. We look forward to you providing us feedback about this solution so that we can make it better and as close to customer’s requirements as possible.

You can provide us feedback at SQL Server Mobile Edition/SQL Server Everywhere Edition MSDN Forum or SQL Server Everywhere Edition Blog - Microsoft SQL Server 2005 Everywhere Edition Access Database Synchronizer (ADS) or email me directly manish.agnihotri@.microsoft.com

Thanks,

Manish

Manish Agnihotri, Program Manager, SQL Server 2005 Everywhere Edition

Good news. But I want to know wherether this ADS can sync over air (Wi-Fi / TCP/IP) by Dll ?

Thanks

|||

Great news but what about SQL Express

Most serious developers dont touch Access

1. Can we modify this to access sql express

2. Or can we do SQL Everywhere synch to another SQL Everywhere database

Thanks

|||

No, you cannot sync over the air. One needs to establish a USB Active sync connection to sync

Regards

Manish

|||

No this cannot be modified to work with SQL Express or SQL Server Everywhere on desktop. This solution is targeted for Microsoft Access only. We had a big ask for this feature from developer community

Regards

Manish

|||

It is so strange that I can't use this tool to convert Access database into SQL mobile database?

I can't find any "Destop Data Sync Wizard" under Start--> Programs. (the readme file tells me to look for this)

The file I installed is SyncWithAccess-EN.msi

|||

Finally, after looked through the posts here, I understand now, I need to compile that sample program then use it. But when I try to open the project, it prompts me an error:

Error retrieving information from user datastore. Platform not found.

Then it said:

The project could not be opened because it refers to a device platform that does not exist in your datastore.

I already installed the WM5 Emulator, when I tried to use Visual Studio to connect to the "WM5 Device", it said sucessfully connected.

I don't have a WM5 PDA yet. The Visual Studio.net 2005 product I installed includes: VB, C#, VC++. I never used C# or VC++ though.

Can anyone tell me what is wrong?

Thanks!

|||

Try installing this:

http://www.microsoft.com/downloads/details.aspx?FamilyID=83a52af2-f524-4ec5-9155-717cbe5d25ed&DisplayLang=en

and this:

http://www.microsoft.com/downloads/details.aspx?FamilyID=dc6c00cb-738A-4B97-8910-5cd29ab5f8d9&DisplayLang=en

Thanks,

Laxmi

|||Consider that SQL Compact is designed to be used "Everywhere" shouldn't you spend a little time designing a device to desktop 1-to-1 sync capability?

Currently most of my work is done on larger Mobile > SQL 2000 / 2005 merge applications, but I would like to leverage the embeddable capabilites of SQL Compact to build a desktop app that communicates to a single mobile device.

Using Access is not desireable. I don't mind that I would be limited to an ActiveSync connection, since only one device / one desktop is the core design consideration I am looking at. I'd would just rather avoid Access. Given the direction that SQL Compact is taking, it looks like the long term idea is to replace the Access engine anyway, why not throw us a bone...

Also, after reading up on the Access sync, it looks like it works very similar to merge replication anyway. Except instesad of running in IIS, it runs as its own service. Thats fine too.. But SQL > SQL is better.

Please let us know if SQL Compact > SQL Compact sync is in the works.|||
Is

there any chance that we could see a sample app or sample code

demonstrating how one might go about creating "Custom Desktop Sync App"

using ADS? There is a very brief explanation on how to do it on the

ADS Readme file which says something about using “ssevas31.dll" and the

ListenSyncRequest but I have not been successful in figuring it out.

Thanks!

Mike|||

I was getting the same message...

"Error retrieving information from user datastore. Platform not found."

... when opening the solution, until I added the WM5 SDK to my machine that alrady had VS2005 Sp1 on it.

However...

The ADS Wizard doesn't seem to be working at all.

My WM5 PPC phone has CF2, SQL CE 3, and the ADS Wizard. All installed without a problem. Laptop's Access 2003 is at SP2. Northwinds file is in c:\

When I go to the phone and connect to the access DB, it returns "Unspecified error [sqlceca30.dll]"

We are terribly interested in understanding more, and would like to see this working.

Anyone?

Cheers

A1ex wrote:

Finally, after looked through the posts here, I understand now, I need to compile that sample program then use it. But when I try to open the project, it prompts me an error:
Error retrieving information from user datastore. Platform not found.
Then it said:
The project could not be opened because it refers to a device platform that does not exist in your datastore.

|||

Have you installed the SQL CE replication components: Either sqlce30.repl.phone.wce5.armv4i.CAB for SmartPhone (non touch screen) or sqlce30.repl.ppc.wce5.armv4i.CAB for PocketPC (touch screen). Sqlceca30.dll is included in these components (see http://www.microsoft.com/sql/editions/sqlmobile/installsdk.mspx)

|||Hi!

I have the following problem: the program runs perfectly in all cases, when my computer is connected to the LAN, without LAN connection the "A request to send data to the computer running IIS has failed" error presents. All settings are the same, IIS is running on the computer, from the computer I can see the http://(computer-name):1024 page... so the service is also running. How can I solve this problem, is there any solution for this? I'm using the program on a laptop, so it's needed....
Thanks|||

Have you added a firewall exception for the default port 1024 which ADS uses for communocating with the device? If not, adding the exception should solve your problem

|||

í can′t find the "Desktop Data Sync Wizard" either, i have installed the ADS without problems, but once done it, can′t find a way to configured it. I have installed it in a Windows XP Professional SP2 with MS Office 2003 and in the mobile device i′m running Windows CE 5.0. What i′m trying to do is to Sync an SDF database with MS Access 2003 in my PC. Is this posible? Should i installed in my PC or into the device or in both? Thank you very much for your help.

Regards,

Roberto.

Friday, February 24, 2012

Custom code problem

I am trying to lear how to use custom coding in a report. I go to the
report properties and type in my code but when I try to access it
suing "Code.MyTestCode", I do not see my function but only a slection
list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
String".
I have tried code as simple as
Public Const MyName = "John"
and then tried to assign it to a text box.
What am I doing wrong or have setup wrong on my system?
TIA,
JohnI have the same question. But when you just invoke the functions inside the
code, should be working fine.
"JL" <john@.marymonte.com> wrote in message
news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>I am trying to lear how to use custom coding in a report. I go to the
> report properties and type in my code but when I try to access it
> suing "Code.MyTestCode", I do not see my function but only a slection
> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
> String".
> I have tried code as simple as
> Public Const MyName = "John"
> and then tried to assign it to a text box.
> What am I doing wrong or have setup wrong on my system?
> TIA,
> John
>|||Hi Patrick,
What do you mean by "invoke the functions inside the
code"?
John
On Fri, 8 Sep 2006 22:44:58 -0400, "Patrick" <feng_online@.hotmail.com>
wrote:
>I have the same question. But when you just invoke the functions inside the
>code, should be working fine.
>"JL" <john@.marymonte.com> wrote in message
>news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>>I am trying to lear how to use custom coding in a report. I go to the
>> report properties and type in my code but when I try to access it
>> suing "Code.MyTestCode", I do not see my function but only a slection
>> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
>> String".
>> I have tried code as simple as
>> Public Const MyName = "John"
>> and then tried to assign it to a text box.
>> What am I doing wrong or have setup wrong on my system?
>> TIA,
>> John
>>
>|||I think the function name isn't showing up in intellisense because it hasn't
been copied to the Private Assemblies folder of VS 2005. Here's a link to
the article that got me going.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
http://www.codeproject.com/dotnet/CustomAssemblies.asp
Steve MunLeeuw
"JL" <john@.marymonte.com> wrote in message
news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>I am trying to lear how to use custom coding in a report. I go to the
> report properties and type in my code but when I try to access it
> suing "Code.MyTestCode", I do not see my function but only a slection
> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
> String".
> I have tried code as simple as
> Public Const MyName = "John"
> and then tried to assign it to a text box.
> What am I doing wrong or have setup wrong on my system?
> TIA,
> John
>|||Hi Steve,
Thanks for the reply. Let me clarify, I am not tryint to use a custom
assembly. I am typing my code into the report properties Code window.
All the samples indicate that it should then show up when I use the
"Code.xxxx" command. I appreciate any suggestions as to what I am
missing or misunderstanding.
Thanks,
John
On Sat, 9 Sep 2006 09:23:35 -0600, "Steve MunLeeuw"
<smunson@.clearwire.net> wrote:
>I think the function name isn't showing up in intellisense because it hasn't
>been copied to the Private Assemblies folder of VS 2005. Here's a link to
>the article that got me going.
>C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
>http://www.codeproject.com/dotnet/CustomAssemblies.asp
>Steve MunLeeuw
>
>"JL" <john@.marymonte.com> wrote in message
>news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>>I am trying to lear how to use custom coding in a report. I go to the
>> report properties and type in my code but when I try to access it
>> suing "Code.MyTestCode", I do not see my function but only a slection
>> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
>> String".
>> I have tried code as simple as
>> Public Const MyName = "John"
>> and then tried to assign it to a text box.
>> What am I doing wrong or have setup wrong on my system?
>> TIA,
>> John
>>
>

Custom Code Access Error

I've created a function Period in custom code block. Then I put expression
=Code!Period()
in the textbox. Then I got this message:
The value expression for the textbox â'textbox6â' contains an error: [BC30367]
Class 'ReportExprHostImpl.CustomCodeProxy' cannot be indexed because it has
no default property.
Am I doing something wrong?
--
Thanks in advance,
IDcode.period not code!period
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"exkievan" <exkievan@.discussions.microsoft.com> wrote in message
news:58940691-9E20-49AE-82E8-4F8BE0092E9C@.microsoft.com...
> I've created a function Period in custom code block. Then I put expression
> =Code!Period()
> in the textbox. Then I got this message:
> The value expression for the textbox 'textbox6' contains an error:
[BC30367]
> Class 'ReportExprHostImpl.CustomCodeProxy' cannot be indexed because it
has
> no default property.
> Am I doing something wrong?
> --
> Thanks in advance,
> ID

custom code + delegation = problem...

Hi,
I have a custom aspx page which access RS (Reporting Services) using the
webservice interface.
I'm using the delegation (defautcredentialcache) to send the user
authentication to RS.
RS and my web application are on the same server.
this works fine in 2 cases:
* NT authentication used in both virtual directories (/reportserver & /
mycustomapp)
* Basic authentication used for /mycustomapp but NT authentication for
/reportserver
But I want to setup the Basic authentication for both the /reportserver & /
mycustomapp virtual folders.
I don't understand why using the default credential cache doesn't works.
my current work around is to detect if I'm in Basic authentication mode,
then a create a networkcredential object base on the current user login &
password.
does it a RS related issue? or an ASP.Net one?Hello,
I've had issues with webservices and credential delegation many times.
I have a quick article I wrote here that may help. If not let me know and
I can try to help out.
article http://www.criticalsites.com/dlaflotte/default.aspx?date=2004-08-31
Good Luck,
Duane
Duane Laflotte
MCSE, MCSD, MCDBA, MCSA, MCT, MCP+I
dlaflotte@.criticalsites.com
http://www.criticalsites.com/dlaflotte
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:%23wKtYcOcFHA.3492@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a custom aspx page which access RS (Reporting Services) using the
> webservice interface.
> I'm using the delegation (defautcredentialcache) to send the user
> authentication to RS.
> RS and my web application are on the same server.
> this works fine in 2 cases:
> * NT authentication used in both virtual directories (/reportserver & /
> mycustomapp)
> * Basic authentication used for /mycustomapp but NT authentication for
> /reportserver
> But I want to setup the Basic authentication for both the /reportserver &
/
> mycustomapp virtual folders.
> I don't understand why using the default credential cache doesn't works.
> my current work around is to detect if I'm in Basic authentication mode,
> then a create a networkcredential object base on the current user login &
> password.
> does it a RS related issue? or an ASP.Net one?
>|||mmm
I allready have tested this without more success.
now the only problem I have is this:
* Frontend custom app using Basic authentication
* Reportserver using both NTLM & Basic authentication
in this case, I send the username + password of the active user, but I
receive a permission error!
I think the system try to use the integrated security...
"Duane Laflotte" <dlaflotte@.criticalsites.com> wrote in message
news:O$tSasOcFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I've had issues with webservices and credential delegation many times.
> I have a quick article I wrote here that may help. If not let me know
> and
> I can try to help out.
> article
> http://www.criticalsites.com/dlaflotte/default.aspx?date=2004-08-31
> Good Luck,
> Duane
>
> --
> Duane Laflotte
> MCSE, MCSD, MCDBA, MCSA, MCT, MCP+I
> dlaflotte@.criticalsites.com
> http://www.criticalsites.com/dlaflotte
>
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:%23wKtYcOcFHA.3492@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have a custom aspx page which access RS (Reporting Services) using the
>> webservice interface.
>> I'm using the delegation (defautcredentialcache) to send the user
>> authentication to RS.
>> RS and my web application are on the same server.
>> this works fine in 2 cases:
>> * NT authentication used in both virtual directories (/reportserver & /
>> mycustomapp)
>> * Basic authentication used for /mycustomapp but NT authentication for
>> /reportserver
>> But I want to setup the Basic authentication for both the /reportserver &
> /
>> mycustomapp virtual folders.
>> I don't understand why using the default credential cache doesn't works.
>> my current work around is to detect if I'm in Basic authentication mode,
>> then a create a networkcredential object base on the current user login &
>> password.
>> does it a RS related issue? or an ASP.Net one?
>>
>|||If the front end server is using basic auth, are you capturing the user name
and password from that and passing those in your NetworkCredential object or
are you using DefaultCredentials?
Joe K.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:uxrxFQZdFHA.1036@.tk2msftngp13.phx.gbl...
> mmm
> I allready have tested this without more success.
> now the only problem I have is this:
> * Frontend custom app using Basic authentication
> * Reportserver using both NTLM & Basic authentication
> in this case, I send the username + password of the active user, but I
> receive a permission error!
> I think the system try to use the integrated security...
> "Duane Laflotte" <dlaflotte@.criticalsites.com> wrote in message
> news:O$tSasOcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I've had issues with webservices and credential delegation many
>> times.
>> I have a quick article I wrote here that may help. If not let me know
>> and
>> I can try to help out.
>> article
>> http://www.criticalsites.com/dlaflotte/default.aspx?date=2004-08-31
>> Good Luck,
>> Duane
>>
>> --
>> Duane Laflotte
>> MCSE, MCSD, MCDBA, MCSA, MCT, MCP+I
>> dlaflotte@.criticalsites.com
>> http://www.criticalsites.com/dlaflotte
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:%23wKtYcOcFHA.3492@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have a custom aspx page which access RS (Reporting Services) using the
>> webservice interface.
>> I'm using the delegation (defautcredentialcache) to send the user
>> authentication to RS.
>> RS and my web application are on the same server.
>> this works fine in 2 cases:
>> * NT authentication used in both virtual directories (/reportserver & /
>> mycustomapp)
>> * Basic authentication used for /mycustomapp but NT authentication for
>> /reportserver
>> But I want to setup the Basic authentication for both the /reportserver
>> &
>> /
>> mycustomapp virtual folders.
>> I don't understand why using the default credential cache doesn't works.
>> my current work around is to detect if I'm in Basic authentication mode,
>> then a create a networkcredential object base on the current user login
>> &
>> password.
>> does it a RS related issue? or an ASP.Net one?
>>
>>
>|||yes.
Dim header As String = Page.Request.ServerVariables("HTTP_AUTHORIZATION")
If header.StartsWith("Basic") Or header = "" Then
oServ.Credentials = New
Net.NetworkCredential(Page.Request.ServerVariables("AUTH_USER"),
Page.Request.ServerVariables("AUTH_PASSWORD"))
Else
oServ.PreAuthenticate = True
oServ.Credentials = Net.CredentialCache.DefaultCredentials
End If
I have also tried with PreAuthenticate = true with basic authentication.
This code works fine if integrated security is disabled for the reportserver
virtual folder (so both web sites used the same security model)
There is a way to force the preferred authentication method used when I call
the webservice?
"Joe Kaplan (MVP - ADSI)" <joseph.e.kaplan@.removethis.accenture.com> wrote
in message news:%23mxnFAadFHA.1448@.TK2MSFTNGP14.phx.gbl...
> If the front end server is using basic auth, are you capturing the user
> name and password from that and passing those in your NetworkCredential
> object or are you using DefaultCredentials?
> Joe K.
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uxrxFQZdFHA.1036@.tk2msftngp13.phx.gbl...
>> mmm
>> I allready have tested this without more success.
>> now the only problem I have is this:
>> * Frontend custom app using Basic authentication
>> * Reportserver using both NTLM & Basic authentication
>> in this case, I send the username + password of the active user, but I
>> receive a permission error!
>> I think the system try to use the integrated security...
>> "Duane Laflotte" <dlaflotte@.criticalsites.com> wrote in message
>> news:O$tSasOcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I've had issues with webservices and credential delegation many
>> times.
>> I have a quick article I wrote here that may help. If not let me know
>> and
>> I can try to help out.
>> article
>> http://www.criticalsites.com/dlaflotte/default.aspx?date=2004-08-31
>> Good Luck,
>> Duane
>>
>> --
>> Duane Laflotte
>> MCSE, MCSD, MCDBA, MCSA, MCT, MCP+I
>> dlaflotte@.criticalsites.com
>> http://www.criticalsites.com/dlaflotte
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:%23wKtYcOcFHA.3492@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have a custom aspx page which access RS (Reporting Services) using
>> the
>> webservice interface.
>> I'm using the delegation (defautcredentialcache) to send the user
>> authentication to RS.
>> RS and my web application are on the same server.
>> this works fine in 2 cases:
>> * NT authentication used in both virtual directories (/reportserver & /
>> mycustomapp)
>> * Basic authentication used for /mycustomapp but NT authentication for
>> /reportserver
>> But I want to setup the Basic authentication for both the /reportserver
>> &
>> /
>> mycustomapp virtual folders.
>> I don't understand why using the default credential cache doesn't
>> works.
>> my current work around is to detect if I'm in Basic authentication
>> mode,
>> then a create a networkcredential object base on the current user login
>> &
>> password.
>> does it a RS related issue? or an ASP.Net one?
>>
>>
>>
>|||As far as I know you can't force the client to only do one authentication
method, but I could be wrong. Typically, the client and server will
negotiate based on what the server accepts and the client can provide.
What happens if you specify a domain in the NetworkCredential (or does the
AUTH_USER header already have a domain name like "domain\user" or a upn like
user@.domain.com?)?
Joe K.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:eHVxsjadFHA.2556@.TK2MSFTNGP10.phx.gbl...
> yes.
> Dim header As String = Page.Request.ServerVariables("HTTP_AUTHORIZATION")
> If header.StartsWith("Basic") Or header = "" Then
> oServ.Credentials = New
> Net.NetworkCredential(Page.Request.ServerVariables("AUTH_USER"),
> Page.Request.ServerVariables("AUTH_PASSWORD"))
> Else
> oServ.PreAuthenticate = True
> oServ.Credentials = Net.CredentialCache.DefaultCredentials
> End If
>
> I have also tried with PreAuthenticate = true with basic authentication.
> This code works fine if integrated security is disabled for the
> reportserver virtual folder (so both web sites used the same security
> model)
> There is a way to force the preferred authentication method used when I
> call the webservice?
>
> "Joe Kaplan (MVP - ADSI)" <joseph.e.kaplan@.removethis.accenture.com> wrote
> in message news:%23mxnFAadFHA.1448@.TK2MSFTNGP14.phx.gbl...
>> If the front end server is using basic auth, are you capturing the user
>> name and password from that and passing those in your NetworkCredential
>> object or are you using DefaultCredentials?
>> Joe K.
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:uxrxFQZdFHA.1036@.tk2msftngp13.phx.gbl...
>> mmm
>> I allready have tested this without more success.
>> now the only problem I have is this:
>> * Frontend custom app using Basic authentication
>> * Reportserver using both NTLM & Basic authentication
>> in this case, I send the username + password of the active user, but I
>> receive a permission error!
>> I think the system try to use the integrated security...
>> "Duane Laflotte" <dlaflotte@.criticalsites.com> wrote in message
>> news:O$tSasOcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I've had issues with webservices and credential delegation many
>> times.
>> I have a quick article I wrote here that may help. If not let me know
>> and
>> I can try to help out.
>> article
>> http://www.criticalsites.com/dlaflotte/default.aspx?date=2004-08-31
>> Good Luck,
>> Duane
>>
>> --
>> Duane Laflotte
>> MCSE, MCSD, MCDBA, MCSA, MCT, MCP+I
>> dlaflotte@.criticalsites.com
>> http://www.criticalsites.com/dlaflotte
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:%23wKtYcOcFHA.3492@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have a custom aspx page which access RS (Reporting Services) using
>> the
>> webservice interface.
>> I'm using the delegation (defautcredentialcache) to send the user
>> authentication to RS.
>> RS and my web application are on the same server.
>> this works fine in 2 cases:
>> * NT authentication used in both virtual directories (/reportserver &
>> /
>> mycustomapp)
>> * Basic authentication used for /mycustomapp but NT authentication for
>> /reportserver
>> But I want to setup the Basic authentication for both the
>> /reportserver &
>> /
>> mycustomapp virtual folders.
>> I don't understand why using the default credential cache doesn't
>> works.
>> my current work around is to detect if I'm in Basic authentication
>> mode,
>> then a create a networkcredential object base on the current user
>> login &
>> password.
>> does it a RS related issue? or an ASP.Net one?
>>
>>
>>
>>
>|||the domain is allready in the auth_user variable.
well... nothing works, so I'm using my workaround.
which is creating a second "reportserver" virtual folder, where only basic
authentication is used, and then there is no problems.
I think RS prefer to use the NT authentication instead-of the basic
authentication.
"Joe Kaplan (MVP - ADSI)" <joseph.e.kaplan@.removethis.accenture.com> wrote
in message news:uwPu75adFHA.4040@.TK2MSFTNGP14.phx.gbl...
> As far as I know you can't force the client to only do one authentication
> method, but I could be wrong. Typically, the client and server will
> negotiate based on what the server accepts and the client can provide.
> What happens if you specify a domain in the NetworkCredential (or does the
> AUTH_USER header already have a domain name like "domain\user" or a upn
> like user@.domain.com?)?
> Joe K.
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:eHVxsjadFHA.2556@.TK2MSFTNGP10.phx.gbl...
>> yes.
>> Dim header As String = Page.Request.ServerVariables("HTTP_AUTHORIZATION")
>> If header.StartsWith("Basic") Or header = "" Then
>> oServ.Credentials = New
>> Net.NetworkCredential(Page.Request.ServerVariables("AUTH_USER"),
>> Page.Request.ServerVariables("AUTH_PASSWORD"))
>> Else
>> oServ.PreAuthenticate = True
>> oServ.Credentials = Net.CredentialCache.DefaultCredentials
>> End If
>>
>> I have also tried with PreAuthenticate = true with basic authentication.
>> This code works fine if integrated security is disabled for the
>> reportserver virtual folder (so both web sites used the same security
>> model)
>> There is a way to force the preferred authentication method used when I
>> call the webservice?
>>
>> "Joe Kaplan (MVP - ADSI)" <joseph.e.kaplan@.removethis.accenture.com>
>> wrote in message news:%23mxnFAadFHA.1448@.TK2MSFTNGP14.phx.gbl...
>> If the front end server is using basic auth, are you capturing the user
>> name and password from that and passing those in your NetworkCredential
>> object or are you using DefaultCredentials?
>> Joe K.
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:uxrxFQZdFHA.1036@.tk2msftngp13.phx.gbl...
>> mmm
>> I allready have tested this without more success.
>> now the only problem I have is this:
>> * Frontend custom app using Basic authentication
>> * Reportserver using both NTLM & Basic authentication
>> in this case, I send the username + password of the active user, but I
>> receive a permission error!
>> I think the system try to use the integrated security...
>> "Duane Laflotte" <dlaflotte@.criticalsites.com> wrote in message
>> news:O$tSasOcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I've had issues with webservices and credential delegation many
>> times.
>> I have a quick article I wrote here that may help. If not let me
>> know and
>> I can try to help out.
>> article
>> http://www.criticalsites.com/dlaflotte/default.aspx?date=2004-08-31
>> Good Luck,
>> Duane
>>
>> --
>> Duane Laflotte
>> MCSE, MCSD, MCDBA, MCSA, MCT, MCP+I
>> dlaflotte@.criticalsites.com
>> http://www.criticalsites.com/dlaflotte
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:%23wKtYcOcFHA.3492@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have a custom aspx page which access RS (Reporting Services) using
>> the
>> webservice interface.
>> I'm using the delegation (defautcredentialcache) to send the user
>> authentication to RS.
>> RS and my web application are on the same server.
>> this works fine in 2 cases:
>> * NT authentication used in both virtual directories (/reportserver &
>> /
>> mycustomapp)
>> * Basic authentication used for /mycustomapp but NT authentication
>> for
>> /reportserver
>> But I want to setup the Basic authentication for both the
>> /reportserver &
>> /
>> mycustomapp virtual folders.
>> I don't understand why using the default credential cache doesn't
>> works.
>> my current work around is to detect if I'm in Basic authentication
>> mode,
>> then a create a networkcredential object base on the current user
>> login &
>> password.
>> does it a RS related issue? or an ASP.Net one?
>>
>>
>>
>>
>>
>

Sunday, February 19, 2012

Custom authorization on a particular report

Hi,
I have created a report that displays order summaries based on a parameter
CustomerName. I have many customers and want to give them all access to this
report. I've installed the Forms Authentication sample and am able to
authenticate, so each customer has a login.
The problem is that I want each customer to be able to view only his own
order summary (i.e. the CustomerName parameter to the report must be set to
the customer's login id and he cannot change it). Is there a way to pass the
username given in Forms Authentication to the report?
Passing it in the URL is no good because users can modify the URL. The
authorization pieces in the Forms Authentication sample seem only to grant or
deny access on to a particular report, but not on parameters to the report.
Any tips would be greatly appreciated.
Thanks,
DonYou can use the global user!userid, don't pass this as a parameter.
Bruce L-C
"Don" <Don@.discussions.microsoft.com> wrote in message
news:37361218-AD5E-4B95-BF84-3C863FFD1738@.microsoft.com...
> Hi,
> I have created a report that displays order summaries based on a parameter
> CustomerName. I have many customers and want to give them all access to
this
> report. I've installed the Forms Authentication sample and am able to
> authenticate, so each customer has a login.
> The problem is that I want each customer to be able to view only his own
> order summary (i.e. the CustomerName parameter to the report must be set
to
> the customer's login id and he cannot change it). Is there a way to pass
the
> username given in Forms Authentication to the report?
> Passing it in the URL is no good because users can modify the URL. The
> authorization pieces in the Forms Authentication sample seem only to grant
or
> deny access on to a particular report, but not on parameters to the
report.
> Any tips would be greatly appreciated.
> Thanks,
> Don

Custom Authorization in Reporting services

For custom authorization, I am overwriting the CheckAccess method.

But how do I know what I am trying to access in CheckAccess method? I need to know what report the check access method is trying to execute for our requirements.

How do I get that?

I have the same issue! Can anyone help?

Thanks!

Custom Authorization in Reporting services

For custom authorization, I am overwriting the CheckAccess method.

But how do I know what I am trying to access in CheckAccess method? I need to know what report the check access method is trying to execute for our requirements.

How do I get that?

I have the same issue! Can anyone help?

Thanks!

Custom Auditing

We are having to implement restrictive security permissions on SQL, some of
which requires that we audit and log any access of any kind for specific
tables and columns. What would be the best way to do this? We can use
triggers to log any changes to specific tables and columns, but we can't use
those when a user is simply retrieving sensitive and encrypted data from a
table. We could lock down access permissions to those tables so that only
authorized users can execute stored procedures, and have code to log such
access in the procs themselves, but we need to ensure that even if the dbo
or sa were to access that data, that it would be logged.
TIA
Michael MacGregor
Database ArchitectMichael,
If you want to log accesses, even of the dbo or sa, then you pretty much
will have to use Profiler. You can put the traces into tables for online
auditing, either directly or by periodically importing the trace files.
There are also probably some third-party auditing tools.
RLF
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OyGkG4tyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> We are having to implement restrictive security permissions on SQL, some
> of which requires that we audit and log any access of any kind for
> specific tables and columns. What would be the best way to do this? We can
> use triggers to log any changes to specific tables and columns, but we
> can't use those when a user is simply retrieving sensitive and encrypted
> data from a table. We could lock down access permissions to those tables
> so that only authorized users can execute stored procedures, and have code
> to log such access in the procs themselves, but we need to ensure that
> even if the dbo or sa were to access that data, that it would be logged.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks again Russell.
MTM

custom assmebly permissions (I really need help)

I try to deploy a custom assembly which access a oracle database.
I' read all post in this news group about it and also the policies refered.
I'm very confused now, becouse it is a huge amount of information and I
wonder how unspecific it is.
I need exactly to know which file I must change how and where exactly in the
file. I tried many combinations, but I have a deadline on the project an I'm
really in troubles.
In the KB Articles are CodeGroups described and also PermissionSets 3 files
to modify described too, but which file must be how modified is not described.
Please help me somebody with detailled information. It really drives me to
despair.The assembly by it self works if I remove the code for access the oracle
database.
In a article is mentoined that the code inside the assembly must be modified
too. But I don't know how to transfer this to my VB Assmebly. But this is
possibly not the only wrong thing in my project.
The purpose of the assembly is localization. And in the database still
exists a table with translations used by the application used to maintain the
data. Therefore I must access the database with the assemly.
And btw. I can not understand why the samples are for file access
permissions. How many people will store f.e. currency information in a xml
file on the server? How does the data uses by RS come usual into the
database? Posibly with existing other applications? Or does RS support data
maintainance?
"Dev Main" wrote:
> I try to deploy a custom assembly which access a oracle database.
> I' read all post in this news group about it and also the policies refered.
> I'm very confused now, becouse it is a huge amount of information and I
> wonder how unspecific it is.
> I need exactly to know which file I must change how and where exactly in the
> file. I tried many combinations, but I have a deadline on the project an I'm
> really in troubles.
> In the KB Articles are CodeGroups described and also PermissionSets 3 files
> to modify described too, but which file must be how modified is not described.
> Please help me somebody with detailled information. It really drives me to
> despair.

Custom Assemply Permissions Problem

I have a custom assembly that I want to use to access some SQL Server data. I'm pretty sure this is a security problem but don't know how to resolve it. This is my function in my assembly (commented most of it out until I get something to work).

PublicSharedFunction DataTest2()AsStringDim permission =New SqlClientPermissionAttribute(Security.Permissions.SecurityAction.Assert)

permission.Assert()

'Dim ds As DataSetDim strReturnValueAsString

strReturnValue =

"OK"'Dim sqlCN As New SqlClient.SqlConnection("server=EOPS; database=RegionAmericas; uid=sa; pwd=thebo$$")'Dim sqlCMD As New SqlClient.SqlCommand'Dim intNumRows As Integer = 0

'strReturnValue = ""'sqlCMD.CommandText = "SELECT DESCRIPTION " & _' "FROM tblCountry WITH (NOLOCK) " & _' "WHERE COUNTRY='US'"

'Dim da As New SqlDataAdapter(sqlCMD)'Try' ds = New DataSet' da.Fill(ds, "Country")' intNumRows = ds.Tables(0).Rows.Count' If intNumRows > 0 Then' strReturnValue = ds.Tables(0).Rows(0).Item(0).ToString' End If'Catch ex As Exception' MsgBox(ex.Message)'End Try

DataTest2 = strReturnValue

EndFunction

Then I added this to rssrvpolicy file (both locally and on the server)

What else do I have to do? The report just shows #error. Thanks, Phill

try including ur assembly in ur global assembly cache.

Custom assembly security problems

Really need some help on this one. I'm trying to deploy a custom assembly that uses ado.net to access an Oracle database. It works fine in the Preview Tab, which isn't affected by security settings but blows up in the Pop-Up Preview window, which is affected by security settings.
I've added a code group to the rsPreviewPolicy file. It appears to load just fine. I've used the .Net configuration tool to grant the assembly full trust. I've upped the security for the IntraNet zone to Full Trust(an approach that worked for a co-worker) I believe my problem isn't the Reporting Services security layer, but the more general CLR layer. I'm new to .Net and am not sure how to proceed.
Any ideas would be appreciated. The project is dead in the water until I get past this.
Thanks
Doug
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.It sounds like you did not explicitly assert permissions to open a database
connection. Unless you assert the permission explicitly in the custom
assembly code, it will fail with a security exception.
Example for opening a connection to a SQL Server:
...
SqlClientPermission permission = new
SqlClientPermission(PermissionState.Unrestricted);
try
{
permission.Assert(); // Assert security permission!
SqlConnection con = new SqlConnection("...");
con.Open();
...
}
You might also want to check out these links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:O9TAc2cbEHA.904@.TK2MSFTNGP09.phx.gbl...
> Really need some help on this one. I'm trying to deploy a custom assembly
that uses ado.net to access an Oracle database. It works fine in the
Preview Tab, which isn't affected by security settings but blows up in the
Pop-Up Preview window, which is affected by security settings.
> I've added a code group to the rsPreviewPolicy file. It appears to load
just fine. I've used the .Net configuration tool to grant the assembly full
trust. I've upped the security for the IntraNet zone to Full Trust(an
approach that worked for a co-worker) I believe my problem isn't the
Reporting Services security layer, but the more general CLR layer. I'm new
to .Net and am not sure how to proceed.
> Any ideas would be appreciated. The project is dead in the water until I
get past this.
> Thanks
> Doug
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Thanks for your response. I tried .Assert with the SQL permissions and got that connection to work. However, I tried the same with the OraclePermission and it's still throwing a SecurityException.
This is a deal breaker for my company and RS. Management is getting very edgy over the deployment/security issues we're dealing with.
Here is my code in the assembly:
OraclePermission op = new OraclePermission(PermissionState.Unrestricted);
op.Assert;
strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
OracleConnection cn = new OracleConnection(strConn);
cn.Open();
This works in the PreviewTab the PopUp Preview but not on the remote Production box.
Any ideas?
Doug
"Robert Bruckner [MSFT]" wrote:
> It sounds like you did not explicitly assert permissions to open a database
> connection. Unless you assert the permission explicitly in the custom
> assembly code, it will fail with a security exception.
> Example for opening a connection to a SQL Server:
> ...
> SqlClientPermission permission = new
> SqlClientPermission(PermissionState.Unrestricted);
> try
> {
> permission.Assert(); // Assert security permission!
> SqlConnection con = new SqlConnection("...");
> con.Open();
> ...
> }
> You might also want to check out these links:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
> http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> news:O9TAc2cbEHA.904@.TK2MSFTNGP09.phx.gbl...
> > Really need some help on this one. I'm trying to deploy a custom assembly
> that uses ado.net to access an Oracle database. It works fine in the
> Preview Tab, which isn't affected by security settings but blows up in the
> Pop-Up Preview window, which is affected by security settings.
> >
> > I've added a code group to the rsPreviewPolicy file. It appears to load
> just fine. I've used the .Net configuration tool to grant the assembly full
> trust. I've upped the security for the IntraNet zone to Full Trust(an
> approach that worked for a co-worker) I believe my problem isn't the
> Reporting Services security layer, but the more general CLR layer. I'm new
> to .Net and am not sure how to proceed.
> >
> > Any ideas would be appreciated. The project is dead in the water until I
> get past this.
> >
> > Thanks
> >
> > Doug
> >
> >
> > --
> > Posted using Wimdows.net NntpNews Component -
> >
> > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
>
>|||If you read the MSDN documentation for the OraclePermission class and the
SqlClientPermission class and compare them, you will notice the following
statement:
"This class [i.e. OraclePermission] is intended for future use when the .NET
Framework Data Provider for Oracle is enabled for partial trust scenarios.
The provider currently requires FullTrust permission."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclepermissionclasstopic.asp
I.e. asserting the OraclePermission won't help you get around the Security
exception, because the .NET data provider for Oracle _must_ have full trust
and does not work in a partially trusted environment. This is inherit to the
current design of the data provider and the Oracle client (running unmanaged
code internally).
Note: The managed provider for SQL Server is enabled for partial trust
scenarios, therefore it is sufficient (and advisable) to just assert the
SqlClientPermission.
Here is how you assert full trust in your custom assembly using an attribute
on the method that opens an Oracle connection:
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// your code
strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
OracleConnection cn = new OracleConnection(strConn);
cn.Open();
// ...
}
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dwelty" <dwelty@.discussions.microsoft.com> wrote in message
news:A1C06F67-7893-4D9D-9AC4-E1E63BDBB8C5@.microsoft.com...
> Thanks for your response. I tried .Assert with the SQL permissions and
got that connection to work. However, I tried the same with the
OraclePermission and it's still throwing a SecurityException.
> This is a deal breaker for my company and RS. Management is getting very
edgy over the deployment/security issues we're dealing with.
> Here is my code in the assembly:
> OraclePermission op = new OraclePermission(PermissionState.Unrestricted);
> op.Assert;
> strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
> OracleConnection cn = new OracleConnection(strConn);
> cn.Open();
> This works in the PreviewTab the PopUp Preview but not on the remote
Production box.
> Any ideas?
> Doug
>
> "Robert Bruckner [MSFT]" wrote:
> > It sounds like you did not explicitly assert permissions to open a
database
> > connection. Unless you assert the permission explicitly in the custom
> > assembly code, it will fail with a security exception.
> > Example for opening a connection to a SQL Server:
> > ...
> > SqlClientPermission permission = new
> > SqlClientPermission(PermissionState.Unrestricted);
> > try
> > {
> > permission.Assert(); // Assert security permission!
> > SqlConnection con = new SqlConnection("...");
> > con.Open();
> > ...
> > }
> >
> > You might also want to check out these links:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
> > http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
> >
> >
> > --
> > Robert M. Bruckner
> > Microsoft SQL Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> > news:O9TAc2cbEHA.904@.TK2MSFTNGP09.phx.gbl...
> > > Really need some help on this one. I'm trying to deploy a custom
assembly
> > that uses ado.net to access an Oracle database. It works fine in the
> > Preview Tab, which isn't affected by security settings but blows up in
the
> > Pop-Up Preview window, which is affected by security settings.
> > >
> > > I've added a code group to the rsPreviewPolicy file. It appears to
load
> > just fine. I've used the .Net configuration tool to grant the assembly
full
> > trust. I've upped the security for the IntraNet zone to Full Trust(an
> > approach that worked for a co-worker) I believe my problem isn't the
> > Reporting Services security layer, but the more general CLR layer. I'm
new
> > to .Net and am not sure how to proceed.
> > >
> > > Any ideas would be appreciated. The project is dead in the water
until I
> > get past this.
> > >
> > > Thanks
> > >
> > > Doug
> > >
> > >
> > > --
> > > Posted using Wimdows.net NntpNews Component -
> > >
> > > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
engine
> > supports Post Alerts, Ratings, and Searching.
> >
> >
> >|||Regarding security permissions: there are typically always two ways to
assert them. Use attributes (as I did in the Oracle code snippet) or create
permission objects and call assert (as I did in the SQL Server code
snippet).
The MSDN documentation contains information for every class and method that
needs certain security permissions at runtime. MSDN also contains several
articles about CAS (code access security) in the .NET framework. Starting
point:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconCodeAccessSecurity.asp
You might also want to check out this recent article in the MSDN magazine:
http://msdn.microsoft.com/msdnmag/issues/04/06/NET/default.aspx
Specifically for Reporting Services you should also read this:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
However, it can be very time consuming to check all classes and function
calls and check which security permissions are needed. The good news is
there are improvements for this in the upcoming .NET 2.0 Framework. Visual
Studio 2005 ("Whidbey") Beta 1 (e.g. download one of the "Express" editions
from http://lab.msdn.microsoft.com/vs2005/) contains a nice command line
tool related to code access security: PermCalc. After installation it is
located at \Program Files\Microsoft Visual Studio 8\Common7\IDE\Permcalc.exe
PermCalc estimates all necessary security permissions that must be granted
for every dll entrypoint in order to avoid security exceptions at runtime.
This estimation is done by performing static IL analysis. Permcalc is also
integrated into the VS Whidbey UI. In order to get the list of necessary
permissions for all custom assembly entry points, you would just run e.g.
permcalc -progress -library CustomAssembly.dll
Therefore you can more easily identify which missing security permission
asserts and grants cause the execution of custom assemblies to fail. Note:
PermCalc can even analyze dlls compiled with older versions of the .NET
Framework (e.g. VS 2003). However, you will need the .NET 2.0 Framework
installed in order to run PermCalc.exe.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"dwelty" <dwelty@.discussions.microsoft.com> wrote in message
news:33B95568-725F-44D6-BFA2-509BDF76A50F@.microsoft.com...
> Robert,
> This worked. Thank you so much. I remember reading at some point in
all this the limitations of the current version of the OraclePermission
class. However, being new to the security model in the .Net framework and
new to RS, I apparently didn't grasp the implications.
> How do you become an expert in all of this? Given my reading of the
documentation that's currently available, I would have taken forever to come
up with that line of code you sent that made all the difference. I'm really
wearying of pasting in snippets of code from various sources and hoping they
work. I'd like to know the how's and why's of this. Do you know of a good
place to start?
> Thanks so much again.
> Doug
>
> "Robert Bruckner [MSFT]" wrote:
> > If you read the MSDN documentation for the OraclePermission class and
the
> > SqlClientPermission class and compare them, you will notice the
following
> > statement:
> >
> > "This class [i.e. OraclePermission] is intended for future use when the
.NET
> > Framework Data Provider for Oracle is enabled for partial trust
scenarios.
> > The provider currently requires FullTrust permission."
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclepermissionclasstopic.asp
> >
> > I.e. asserting the OraclePermission won't help you get around the
Security
> > exception, because the .NET data provider for Oracle _must_ have full
trust
> > and does not work in a partially trusted environment. This is inherit to
the
> > current design of the data provider and the Oracle client (running
unmanaged
> > code internally).
> > Note: The managed provider for SQL Server is enabled for partial trust
> > scenarios, therefore it is sufficient (and advisable) to just assert the
> > SqlClientPermission.
> >
> > Here is how you assert full trust in your custom assembly using an
attribute
> > on the method that opens an Oracle connection:
> >
> > [PermissionSet(SecurityAction.Assert, Unrestricted=true)]
> > public foo()
> > {
> > // your code
> > strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
> > OracleConnection cn = new OracleConnection(strConn);
> > cn.Open();
> > // ...
> > }
> >
> >
> > --
> > Robert M. Bruckner
> > Microsoft SQL Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> >
> >
> > "dwelty" <dwelty@.discussions.microsoft.com> wrote in message
> > news:A1C06F67-7893-4D9D-9AC4-E1E63BDBB8C5@.microsoft.com...
> > > Thanks for your response. I tried .Assert with the SQL permissions
and
> > got that connection to work. However, I tried the same with the
> > OraclePermission and it's still throwing a SecurityException.
> > >
> > > This is a deal breaker for my company and RS. Management is getting
very
> > edgy over the deployment/security issues we're dealing with.
> > >
> > > Here is my code in the assembly:
> > >
> > > OraclePermission op = new
OraclePermission(PermissionState.Unrestricted);
> > > op.Assert;
> > > strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
> > > OracleConnection cn = new OracleConnection(strConn);
> > > cn.Open();
> > >
> > > This works in the PreviewTab the PopUp Preview but not on the remote
> > Production box.
> > >
> > > Any ideas?
> > >
> > > Doug
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > It sounds like you did not explicitly assert permissions to open a
> > database
> > > > connection. Unless you assert the permission explicitly in the
custom
> > > > assembly code, it will fail with a security exception.
> > > > Example for opening a connection to a SQL Server:
> > > > ...
> > > > SqlClientPermission permission = new
> > > > SqlClientPermission(PermissionState.Unrestricted);
> > > > try
> > > > {
> > > > permission.Assert(); // Assert security
permission!
> > > > SqlConnection con = new SqlConnection("...");
> > > > con.Open();
> > > > ...
> > > > }
> > > >
> > > > You might also want to check out these links:
> > > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
> > > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
> > > >
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
> > > >
> > > >
> > > > --
> > > > Robert M. Bruckner
> > > > Microsoft SQL Server Reporting Services
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> > > > news:O9TAc2cbEHA.904@.TK2MSFTNGP09.phx.gbl...
> > > > > Really need some help on this one. I'm trying to deploy a custom
> > assembly
> > > > that uses ado.net to access an Oracle database. It works fine in
the
> > > > Preview Tab, which isn't affected by security settings but blows up
in
> > the
> > > > Pop-Up Preview window, which is affected by security settings.
> > > > >
> > > > > I've added a code group to the rsPreviewPolicy file. It appears
to
> > load
> > > > just fine. I've used the .Net configuration tool to grant the
assembly
> > full
> > > > trust. I've upped the security for the IntraNet zone to Full
Trust(an
> > > > approach that worked for a co-worker) I believe my problem isn't
the
> > > > Reporting Services security layer, but the more general CLR layer.
I'm
> > new
> > > > to .Net and am not sure how to proceed.
> > > > >
> > > > > Any ideas would be appreciated. The project is dead in the water
> > until I
> > > > get past this.
> > > > >
> > > > > Thanks
> > > > >
> > > > > Doug
> > > > >
> > > > >
> > > > > --
> > > > > Posted using Wimdows.net NntpNews Component -
> > > > >
> > > > > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
> > engine
> > > > supports Post Alerts, Ratings, and Searching.
> > > >
> > > >
> > > >
> >
> >
> >

custom assembly permissions

I wrote a custom assembly which access a oracle server database (ODBC). The
report failes on the RS Server, not in the Report Designer.
The problem is the database access, without it the assembly works.
I modified RSReportServer.config and rssrvpolicy.config. I read everything
I could find about the subject (spend more then 50 hours). But I'm still poke
around in the fog.
Can somebody instruct me by give me the exact code he is using in the config
files and in the assembly? It seems the code must be modified too, I tried
but it doesn't work.
Thanks in advance!Right now, the .NET Framework provider for ODBC is not yet enabled for
partial trust scenarios. It currently requires FullTrust permissions (this
will change in a future version). So, unless your custom assembly has
FullTrust permissions, it won't work because asserting OdbcPermission has no
effect (details can also be found on MSDN: OdbcPermission class). In the
particular case of using ODBC to connect to Oracle you definitely need to
give the assembly FullTrust permissions and assert them inside the custom
assembly (see code snippet below which asserts the FullTrust permission
inside the custom assembly via an attribute):
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// ...
OdbcConnection cn = new OdbcConnection("..... connection string");
cn.Open();
// ...
}
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:09D9ED7E-5473-4876-BEFF-47234BCAA4BE@.microsoft.com...
>I wrote a custom assembly which access a oracle server database (ODBC). The
> report failes on the RS Server, not in the Report Designer.
> The problem is the database access, without it the assembly works.
> I modified RSReportServer.config and rssrvpolicy.config. I read
> everything
> I could find about the subject (spend more then 50 hours). But I'm still
> poke
> around in the fog.
> Can somebody instruct me by give me the exact code he is using in the
> config
> files and in the assembly? It seems the code must be modified too, I tried
> but it doesn't work.
> Thanks in advance!

Tuesday, March 27, 2012

Customize the reporting services interface display reports.

Thursday, March 22, 2012

Custom Task: How to access/modify the variables of a package

Tuesday, March 20, 2012

Custom server permissions?

Monday, March 19, 2012

Custom Report Item and embedded images

Sunday, March 11, 2012

Custom protection of rows (problem with updatability of viwes with "WITH VIEW_METADATA" an

Thursday, March 8, 2012

Custom Function in SQL Statement?

Wednesday, March 7, 2012

custom error page

custom error page

Custom Desktop Application

Friday, February 24, 2012

Custom code problem

Custom Code Access Error

custom code + delegation = problem...

Sunday, February 19, 2012

Custom authorization on a particular report

Custom Authorization in Reporting services

Custom Authorization in Reporting services

Custom Auditing

custom assmebly permissions (I really need help)

Custom Assemply Permissions Problem

Custom assembly security problems

custom assembly permissions

Sql Customizing Report

Blog Archive

About Me