Tuesday, July 29, 2008

SQL Server Reporting Services Datasource Security


Stored Credentials
You can store the credentials used to access an external data source. Credentials are stored in reversible encryption in the report server database. You can specify one set of stored credentials for each data source used in a report. The credentials you provide retrieve the same data for every user who runs the report.

Stored credentials are recommended as part of a strategy for accessing remote database servers. Stored credentials are required if you want to support subscriptions, or schedule report history generation or report snapshot refreshes. When a report runs as as a background process, the report server is the agent that executes the report. Because there is no user context in place, the report server must get credential information from the report server database in order to connect to a data source.

The user name and password that you specify can be Windows credentials or a database login. If you specify Windows credentials, the report server passes the credentials to Windows for subsequent authentication. Otherwise, the credentials are passed to the database server for authentication.

Windows Integrated Security
When you use the Windows Integrated Security option, the report server passes the security token of the user accessing the report to the server hosting the external data source. In this case, the user is not prompted to type a user name or password.

This approach is recommended if Kerberos is enabled. If Kerberos is not enabled, you should only use this approach if all the servers that you want to access are located on the same computer.

In other words each user would need to be added access to the Database Hosted on SQL Server

Wednesday, July 02, 2008

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified or Named Pipes Provider, error: 40 - Could not open a connection to SQL S

The problem was that the remote connection needed to utilize Named Pipes, but in order to utilize Named Pipes both the machine hosting the instance of SQL and the my local machine must have the alias:

With Supports instruction I went to SQL Server configuration Manager, expanded SQL Native Client Configuration and right clicked Aliases and created the alias on the machine and did the same for my personal machine and what do you know, it worked.

Tuesday, July 01, 2008

How to determine what ports are open

start -> run -> cmd
netstat -na

look for :3306 and LISTEN on the same line...

OR..also from command prompt

telnet localhost 3306