Login failed for user 'xxxx'. Cannot open user default database. Login failed.

Running a report on sqlserver RS 2008 R2 that uses a data source that looks at a database on a sql express 2012 server.

I can run the report in preview mode from bits on the sql 2008 r2 server.

I have tested the connection of the  deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.

I can logon to sql express 2012 using  management studio and logon as the user and access the database run stored procedures etc.

windows server 2003, sql server 2008 R2 reporting services server.

windows server 2012 sql express 2012 data source database location.

July 21st, 2015 5:30pm

Hi,

Can you provide the error logs from the below location

%ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\LogFiles

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Free Windows Admin Tool Kit Click here and download it now
July 21st, 2015 10:29pm

It might be possible that in your Report server configuration settings you are using some execution account. 

In that case you have to give that execution account access to SQL Server DBs

July 21st, 2015 10:36pm

I have tested the connection of the  deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.

Check you have Proper rights...

Free Windows Admin Tool Kit Click here and download it now
July 21st, 2015 11:21pm

Thanks for the reply,

from log file.

processing!ReportServer_0-440!1b14!07/22/2015-08:25:29:: e ERROR: An exception has occurred in data set 'DataSet1'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DataSet1'. ---> System.Data.SqlClient.SqlException: Login failed for user 'reporting'.
Cannot open user default database. Login failed.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
   at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)

July 21st, 2015 11:35pm

Hi,

Can you run the below query and see what's the default database for  your user

SELECt * FROm sys.server_principals

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 12:02am

The default database is the one I am trying to connect to in the datasource.
July 22nd, 2015 12:14am

Edit your report at your  server using report builder and see whether you are able to connect to the run your query.
Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 1:07am

Hi Ghw123, 

According to your description, when previewing the report, you get an error Login failed for user 'reporting', right? 

In our local environment, we have tested this scenario. In your scenario, are you using a shared data source in your report? If so, please open the shared data source and go to Credentials tab. If the user name or password are incorrect, Reporting Services will throw this error. So please check the credential for the shared data source. 

If you have any question, please feel free to ask. 

Regards, 
Shrek Li

July 22nd, 2015 5:31am

Hi Shrek, yes we tried that first connected successfully to another database that the user connects to.
Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 4:10pm

Are you using  Oledb provider ?? If yes then that might be causing a issue.Because it does not support backward compatability .Use SQL Server Native Client instead.

If the above is not a issue

open report configuration manager

and try to establish connection from there as seen in the below screenshot

July 22nd, 2015 4:43pm

Your account must have acces to the db .

Its account mapping issue .Check if the db you are trying to hit has your login

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 4:51pm

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

Other recent topics Other recent topics