How to store credentials for a data source in SQL Server 2008?

1
votes

I have created the datasource in the SSRS by using option Credentials stored securely in the report server then I am accessing it through report builder to create report, included datasource then need to create a dataset using option Use a dataset embeded in my report Selecting included datsource and when clicks on Query Designer... its asking me Data souce credentials username and password.

According to requirement we are allowing user to use the Datasource but do not want to share the credentials in that case how should I stored the credentials for a datasource in SQL Server 2008.

I have found link http://msdn.microsoft.com/en-us/library/ms159736.aspx but this is showing answer only for SQL Server 2012 & 2005 not for 2008.

2
reporting-servicesdatasourcereportbuilder
In the report manager, go to the directory where your data sources are, click on the one you want to have stored credentials and check if "credentials stored on the report server" are checked. - kyooryu
I have already done the same thing still its not working. - shan
So now try adding shared data source to your report project, and in credentials tab select "prompt for credentials". You don`t have to specify credentials in connectionstring. Use this datasource in the reports you are deploying. - kyooryu
I am giving capability to external user to create a report, in that case I do not want to share db credentials with them, that's why I am asking how the external user can query in reportbuilder against datasource. kindly read above question again for details. - shan
I have raised request to the Microsoft and they said for dataset embeded in my report credentials require and user have to manually type this is by design. - shan

2 Answers

1
votes

I have raised request to the Microsoft and they said for dataset embeded in report credentials require and user have to manually type this is by design.

0
votes

Try to create a data source in the report with 'Use a connection embedded in my report', providing only the Data Source property in the connectionstring.

Do not try to test the connection as it will ask for credentials, just deploy the report on the server and look if it will work.