I created report which will be used by many customers. Each customer has its own SQL Server database. So in report I implemented embedded data source which gets connection string from report parameter. This report is called from web application using report viewer component. Web application sets correct connection string as parameter. In connection string there is server name, database name and I would also like to include username and password.

I tried it with SQL login and it worked as expected. But I have problems when I use domain username and password. There exists domain user for every customer and it has read rights on its database. When I set connection string parameter as "Data source=serverName;Initial Catalog=dbName;User Id=mydomain\user;Password=password;" I always get "login failed for mydomain\user" error. Is this even possible to implement? If you store credentials securely on report server it is possible do use domain user and password so I assumed there should be possibility to also set it in connection string.

I think  you need to create a windows group on the host server and add to all need AD users. Then you need grant an appropriate permissions  to that group in report manager

https://msdn.microsoft.com/en-us/ms156034.aspx

Domain users already have permissions on report server (Admin role on server and Browser role on report). Maybe I wasn't clear enough with error description. User can call a report without problems. My problem is inside report when data source tries to connect to database.

This is error:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'MyDS'. (rsErrorOpeningConnection)
Login failed for user 'myDomain\myUser'.

I think it wants to connect using SQL Database Authentication with domain user which doesn't work. Probably I need to somehow tell it that this is Windows user.

• Edited by Matevzg 21 hours 10 minutes ago Additional explanation

Hi,

Try to create one datasource in your report solution and see if you are able to connect there by giving domain\username, if it's connect there then it should also connect from your application. I think it just not able to connect to sql server with passed credentials.

Hi Matevzg,

The error message you have provided indicated that the user don't have permission to the db.

As you mentioned that you have select the "SQL Server Authentication" which enabled to include the username and password in the connection string and I have tested on my local environment and the issue caused by you have create the login user of "mydomain\user" to be "Windows Authentication" and when you select the "SQL Server Authentication" in the datasource like below you will got the error:

So, please try to change to the "Windows authentication" in the Datasource connection properties setting or you can create an new login for the "mydomain\user" to use the "SQL Server authentication":


If you still have any problem, please feel free to ask.

Regards,
Vic