Dynamic data source connection string with domain user

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.

May 26th, 2015 5:08am

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


Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 5:16am

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 50 minutes ago Additional explanation
May 26th, 2015 5:50am


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.

Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 8:50am

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.


May 26th, 2015 10:36pm

As I wrote in my first post I am using dynamic connection string which I get from report parameter. So there is no data source connection in design time where I could set authentication type. This is my data source definition:

And credentials tab:

If I set connection string parameter to: "Data source=serverName;Initial Catalog=dbName;User Id=sa;Password=saPassword;" then it works.

If I set connection string parameter to: "Data source=serverName;Initial Catalog=dbName;User Id=mydomain\user;Password=password;" then it doesn't work.

As you suggested I tried to create new login on my database server for domain user to use "SQL Server authentication" but I can't. I get this error:

Free Windows Admin Tool Kit Click here and download it now
May 27th, 2015 3:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics