User connecting to reportserver database from reportserver

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 8, 2011 at 4:29 pm

  #247655

  Hi,

  I have installed SQL Server 2005 reporting services in Server A and it's databases reportserver & reportserverTempDb are on Server B (clustered instance)

  reporting services service runs under a doman account called abc\mssqlsrv and SQL Server service on Server B also runs under same account abc\mssqlsrv.

  From Reporting Services configuration manager, While configuring database setup, I used the below credentilas:

  Credentials Type: Service Credentials.

  So in this case, when a report runs, then the user connecting to the reportserver should be abc\mssqlsrv right?

  But in my case, I'm seeing a user called abc\serverA$ is connnecting to reportserver database (abc\serverA$ is coming from Server A)

  Could you please explain me from where this user invoking?

  why the account abc\mssqlsrv is not making connection to report server database?

  Thanks

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  November 8, 2011 at 4:52 pm

  #1404911

  What are you specifying in your datasources?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 8, 2011 at 4:54 pm

  #1404913

  Data source is an Oracle database.

  The user abc\serverA$ is running the below query in reportServer database:

  CREATE PROCEDURE [dbo].[GetMyRunningJobs]

  @ComputerName as nvarchar(32),

  @JobType as smallint

  AS

  SELECT JobID, StartDate, ComputerName, RequestName, RequestPath, SUSER_SNAME(Users.[Sid]), Users.[UserName], Description,

  Timeout, JobAction, JobType, JobStatus, Users.[AuthType]

  FROM RunningJobs INNER JOIN Users

  ON RunningJobs.UserID = Users.UserID

  WHERE ComputerName = @ComputerName

  AND JobType = @JobType

  Please advice

  thanks

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  November 8, 2011 at 4:56 pm

  #1404916

  If abc\serverA$ is specified as the user account in your data source, then that is the account that will connect to your database.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 8, 2011 at 5:05 pm

  #1404919

  If abc\serverA$ is specified as the user account in your data source, then that is the account that will connect to your database.

  abc\serverA$ is mentioned no where.

  Data source is an Oracle database.

  My question is why abc\serverA$ is connecting to reportserver databse when I configured the database setup to connect the reportserver database using service credentials (abc\mssqlsrv)?

  thanks

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  November 8, 2011 at 5:07 pm

  #1404920

  When you created the report, you had to create a data source. What is the definition of your data source that you created in SSRS for this report?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 8, 2011 at 5:16 pm

  #1404923

  When you created the report, you had to create a data source. What is the definition of your data source that you created in SSRS for this report?

  The user abc\serverA$ is NOT connecting to reportserver database when I run the report. But sometimes, I see the the user abc\serverA$ connecting report server database and running the below procedure:

  CREATE PROCEDURE [dbo].[GetMyRunningJobs]

  @ComputerName as nvarchar(32),

  @JobType as smallint

  AS

  SELECT JobID, StartDate, ComputerName, RequestName, RequestPath, SUSER_SNAME(Users.[Sid]), Users.[UserName], Description,

  Timeout, JobAction, JobType, JobStatus, Users.[AuthType]

  FROM RunningJobs INNER JOIN Users

  ON RunningJobs.UserID = Users.UserID

  WHERE ComputerName = @ComputerName

  AND JobType = @JobType

  The data source is Oracle as below:

  Connection Type: Oracle

  Connection String: Data Source=MyOraDb;Unicode=True

  Credentials stored securely in the report server

  Username : orauser

  password: xxxxxx

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  November 8, 2011 at 5:20 pm

  #1404924

  Is that code a part of a report?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 8, 2011 at 6:11 pm

  #1404929

  I just ran the profiler.

  When I ran a report from report manager, user abc\serverA$ is making connection to reportserver database but not abc\mssqlsrv.

  Profiler output:

  RowNumberEventClassTextDataApplicationNameNTUserNameLoginName

  4810exec ObjectExists @Path=N'/Apps',@AuthType=1Report ServerSERVERA$ABC\SERVERA$

  Is that correct? I appreciate your answer on this.

  thanks