Monthly Archives: February 2007

Avoiding @@IDENTITY

Posted on by

When you are trying to get the last Identity value in SQL Server, the old way was to use the @@IDENTITY variable.

This, however, can easily give you the wrong answer back.  For example, if you have a trigger inserting a history record that also contains an identity column.

Here is an article that discusses why you should probably stick to SCOPE_IDENTITY().

Problems with SQL RS on Windows 2000

Posted on by

I was having problems with getting RS to work correctly on a windows 2000 server.  I would browse to the page, and it would prompt me for my credentials.  I would use the Admin loign, and it wouldn’t take, finally giving me an error like this:

Server Error in ‘/Reports’ Application.
——————————————————————————–

Access to the path “C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportManagerbin” is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.UnauthorizedAccessException: Access to the path “C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportManagerbin” is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate=”true”/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

I found someone who figured out the impersonation problem:

The problem happens when installing RS on Windows 2000 Server that is a domain controller.  RS doesn’t do the setup correctly.  The basic steps to fix the problem are

1. Grant impersonate privilege to IWAM_<machine> account (see knowledge base article 824308).  Go to domain controller security policy  in administrative tools. Click security settings -> Click local policies -> click user right assignment.  In the right pane, double click impersonate a client after authentication.  In security policy setting window, click define these policy settings.  Click add -> click browse.  In select users or groups window, select IWAM account name and click add.  Then, click Ok -> Click OK -> Click OK.  At command prompt, type the following command: secedit /refreshpolicy machine_policy /enforce.  Then, type iisreset.

2.  Remove IWAM_<machine> account from guest group.  Go to active directory users and computers in administrative tools.  Open users folder.  In right pane, double click IWAM_<machine>.  Select member of tab.  Remove guest.  Click OK.

3.  Reboot.

4.  Run rsactivate.  From command prompt, change directory to C:Program FilesMicrosoft SQL Server80ToolsBinn.  The run following command: RSActivate.exe -c “c:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerRSReportServer.config” -t.

See the following link for more details http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSINSTALL/htm/gs_installingrs_v1_8k82.asp .

For now, this seems to have fixed this problem.