Sql-server – Implementing Windows authentication with Reporting Services

authenticationiisreporting-servicessql serverwindows

This is an extension of post Reporting Services Authentication advice sought as I am unable to submit a lengthy post on that thread (apologies admin).

Basically, I am configuring my local installation of reporting services to use Windows Authentication (with the ultimate aim of implementing this authentication on the network).

My current setup:

IIS (5.1): I have created an application named "ReportServer" that uses ASP.NET v2.0.50727. The security for this application is set to "Integrated Windows authentication". I have unchecked the "Anonymous access" check box. Am I correct to assume this will prevent anonymous access to IIS (i.e. to the ReportServer application in IIS?) only. Therefore, the user will be prompted for log in details, which in turn will be validated against windows/AD?

Report: I have created a shared data source where the database resides on a different server and configured the data source to use "Windows Authentication" (not SQL Server authentication) and I can test connection successfully. I can also successfully create and test a simple report by running the report in BIDS.

When I deploy the report, I do not get prompted to login (which is fine). I believe this is due to the fact the application directory in IIS is configured to use "Integrated Windows authentication" which is using the logged in PC user account (correct?).

Whenever I load reporting services via a browser, I am correctly being prompted for my windows domain username and password but only when I run the report from my local PC. Once authenticated, I can see all reports applicable to me. If a colleague attempts to connect to the report instance on my PC, he allowed in without having to log in!! Why is this?!

However, when I run the deployed report via a browser, I receive the error "An error has occurred during report processing. Cannot create a connection to data source . Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." If I change the data source so it uses SQL Server Authentication and I specify a login that exists in SQL Server, the report can be run successfully in a browser.

The ideal solution I would like to implement is as follows:

A user loads the report server URL in a browser.
The user is prompted for their Windows/AD credentials (including domain prefix).
Behind the scenes the user is allowed into the report server.
The user will only be able to see the report folders and reports he/she is allowed to see. This will be controlled by adding the allowed AD groups/users to the report folders and reports (I know how to do this bit). I do not wish to have to maintain separate SQL Server user accounts.

I feel as if I am almost there, i.e. connection to the report server is prompting the user for their Windows credentials before allowing them access to the report server.

Please could someone tell me:

a) Why am I being prompted for my windows log in when I access the reports via a browser locally, but a different windows user is not prompted for a log in when they remotely access my report server via a browser window?

b) If a better overall solution should be implemented, please could you advise or point me to relevant resources.

c) Is there anything to be wary of with my current setup that may cause problems in the future.

Many thanks in advance.

JFB

Best Answer

There are 2 security aspects to deal with

Authentication for the User Accessing the Report
Authentication for the Data Source(s) accessing the database(s)

Typically, we've found that we want to kick unauthorized users out at point #1, and then (assuming that user is authorized), provide a static set of connection credentials for the data source - this has benefits such as allows connection pooling and saves our DBA's a ton of headache figuring which users can access which databases, and run which report SPROC's etc, since they only need to configure for the 'Report Data Source user'.

Point #1 is controlled by the Groups / Users added to the Report / Folder.

Point #2 can be done by setting up a domain credential for your Data Source and then assigning this user appropriate read / execute access on the various databases it needs to access.

Possibly the one point you may be missing is that your data source credential runs as needs 'log on locally' permissions in order to work?

FWIW our settings for the data sources are as follows:

Credentials stored securely in the report server
Check : Use as Windows credentials when connecting to the data source

The account running your

New answer, powershell

TCP

Get-Process -Id (Get-NetTCPConnection -LocalPort YourPortNumberHere).OwningProcess

UDP

Get-Process -Id (Get-NetUDPEndpoint -LocalPort YourPortNumberHere).OwningProcess

Old answer, cmd

 C:\> netstat -a -b

(Add -n to stop it trying to resolve hostnames, which will make it a lot faster.)

Note Dane's recommendation for TCPView. It looks very useful!

-a Displays all connections and listening ports.

-b Displays the executable involved in creating each connection or listening port. In some cases well-known executables host multiple independent components, and in these cases the sequence of components involved in creating the connection or listening port is displayed. In this case the executable name is in [] at the bottom, on top is the component it called, and so forth until TCP/IP was reached. Note that this option can be time-consuming and will fail unless you have sufficient permissions.

-n Displays addresses and port numbers in numerical form.

-o Displays the owning process ID associated with each connection.

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.