Month: July 2015

The report server can not open a connection to the report server database while opening reports in sql server database

Below is the error which I faced at one of my client while opening SQL server reports.

Error:

  • While opening reports from browser,faced the below error.

Error

Issue Reported:

  • The report server can not open a connection to the report server database.While opening reports from url.

Analysis:

  • The report server database or user database which was used for report server that was not present on sql server due to database rebuild operation had been performed by some DBA.
  • Below is the screen shot for the same while configuring database from Report server configuration manager.

Report doen not exist

  • Here,the master database was got corrupted.In this case we have two options either we can replace the master from its backup if taken any else rebuild it.
  • When we rebuild the system database,all user database and report server,report server tempdb database get detached from server also the new system database get generated.
  • You can get the more info as well as how to rebuild the system databases from below Microsoft link

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Solution:

  1. First attach the all user databases along with report server and report server tempdb database.
  2. Now open Report server configuration manager.
  3. Open database page in Report server configuration manager.
  4. Click on change database.

change database

5.Choose an exist report server database and click next.

choose an existing

6.Test the connection and click next.

test connection

7.

next

8.Now specify the credential of an existing account that the report server will use to connect to the report server database click next and finish.

specify credential

9.Now open the reports from url.You will get the below error because after rebuild the data source has been lost.

data source error

10.Now go to home folder of report server from url itself and create a new data source.Put the proper connection string and hit okay button.

new data source

11.Okay now open the reports and you will get your reports back.

final reports

  • This is how you can resolve when you face the issue described above.
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.

User Domain\User Account does not have required permissions error while migrating reports from one server to another server

Below is the error which I faced at one of my client while migrating SQL server reports from one server to another server.

Error:

  • While migrating SQL server reports from one server to another server faced the below user permission error.

ssrs_edelweiss_issue

Issue Reported:

  • While Migrating reports from one sql server to another server the Domain\user permission error thrown.

Analysis:

  • The old SQL server had twelve reports which needed to be deployed on new SQL server.While migrating reports from one server to another server,we open the project solutions  from BI which were containing the whole twelve reports.
  • Once solution opened in BI,then rebuild and deployment done successfully.
  • But while opening reports from Internet Explorer we got the error ‘User doen not have required permissions.Verify that sufficient permissions have been granted and Windows User Account Control(UAC) restrictions have been addressed’.

Solution:

1.Right click on Internet explorer and open it with ‘run as administrator’

2.Open the reports server URL here.

Example:

http://localhost/reports

3.Go to setting and add new assignment

4.Add the user for which you getting the permission error.

5.Give it to appropriate permissions(system admin).

6.Now simply open the Internet explorer.Here you will be able to see the deployed reports.

Note:

But while opening the reports you may get the data source error.

1.So make sure that each reports belong to the correct data source by going in to the security option of the reports one by one.

  • This is how you can resolve when you face the issue described above.
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.

COM+ error while installing sql server 2005 64-bits during migration from sql server 2005 32-bits on the same machine

Below is the error which I faced at one of my client while installing sql server 2005 64-bits during migration from sql server 2005 32-bits on the same machine.

Error:

  • COM+ error while installing SQL Server 2005 64-bits during migration from SQL Server 2005 32-bits on the same machine.

Issue Reported:

  • While installing sql server 64-bits during migration from sql server 32-bits on same machine,throws the error of COM+

Analysis:

  • Failed to install and configure assemblies C:\Program Files\Microsoft SQL
    Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog.
    Error: -2146233087
    Error message: Unknown error 0x80131501
    registered as 32 bit components in the target application. You must install
    the 64 bit versions of the components being installed in a different COM+
    application, or delete the existing 32 bit versions of the components being
    installed from the target COM+ application prior to attempting install of the
    64 bit versions. COM+ applications cannot contain bit neutral components.

Solution:

1. Open Administrative Tools\Component Services.

2. Go to Component Services\Computers\My Computer\COM+ Applications.

3. Delete Microsoft.SqlServer.MSMQTask.

  • This is how you can resolve when you face the issue described above.
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.

Send login expiration notification alerts in sql server

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to send login expiration notification alerts in SQL Server.

Scenario: SQL Server login policies are same as the windows login policies.Its inherit the property from windows login/password policies.

Now our target is to send mail notification before SQL Server login password get expired on production environment to avoid down time.

Infrastructure: Below queries and processes are tested with SQL server2005,2008,2008 R2,2012.

Steps:

  • I am going to put some value in newly and one time created table from below [sql_logins] table.You can check the column and needed value by firing below query.
    select * from sys.sql_logins
  • /*Create the table in any user/master database*/create table ExpiryNote
    (
    Sr_No int identity(1,1) not null,
    ServerName varchar(40) default @@servername,
    ServerType varchar(20) default ‘TestServer’, –server type whether Test Or Live Server
    [LoginName] sysname,
    LastModifiedDate datetime,
    DaysFromLastModified int –this will be our days of password expiration

    )

  • Kindly download above table script from below link.

https://app.box.com/s/njtims8qajtsxjrwu3efxlmo6fwbhhj0

  • /* create this procedure on respective DB and run it on daily basis before 2nd stored procedure.
    Simply create a job having two steps in it.1st step will run below 1st stored procedure and on successfully step,2nd stored procedure will be getting run in 2nd step.

Created by: Dharmesh Mishra
Date/Time:2nd July 2015,09:00 PM
Like my fb page:www.facebook.com/sqlserverkillers
Visit my blogs:lapsql.wordpress.com

  • */
    create procedure [sp_before_sp_PasswordExpiryMadeByDharmesh]
    as
    begin
    truncate table expirynote ——–will truncate the table firstINSERT INTO ExpiryNote ———————then will start puting new data
    (LoginName,LastModifiedDate,DaysFromLastModified)
    SELECT name,modify_date,DATEDIFF(DAY,modify_date,GETDATE())
    FROM sys.sql_logins
    where name in (‘test’)—Enter the login name for which you want to send mail notification before expiration
    end
  • Kindly download above 1st stored procedure from below location.

https://app.box.com/s/074916ij2jc8v00sm5k7clwvrarois04

  • /*Run this procedure in 2nd step in sql job*/create Procedure [sp_PasswordExpiryMadeByDharmesh]
    as
    begin
    declare @recipients varchar(max)
    declare @message varchar(5000)
    declare @query varchar(max)
    declare @subject varchar(112)
    declare @attach_results varchar(50)

    set @recipients = ‘dharmesh@xyz.com’
    set @message = ”
    set @query = ”
    set @subject = ‘Home_Pass_Expiry_Auto_Alert’
    set @attach_results = ‘True’

    IF Exists(select 1 from ExpiryNote)
    begin
    ———- case 2 starts
    begin

    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    begin

    SET @xml = CAST((SELECT [Sr_No] AS ‘td’,”,[ServerName] AS ‘td’,”,[ServerType] AS ‘td’ ,”,[LoginName] AS ‘td’ ,”,[LastModifiedDate] AS ‘td’,”,[DaysFromLastModified] as ‘td’ from
    (select * from ExpiryNote) tab
    FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))
    SET @body ='<html><body><H3>
    Home_Pass_Expiry_Auto_Alert</H3>
    <tr>This is system generated Report, Please do not reply.</tr>
    <table border = 1>
    <tr bgcolor=”#C0C0C0″>
    <th> Sr_No </th> <th> ServerName </th> <th>ServerType </th> <th> LoginName</th> <th> LastModifiedDate </th> <th> DaysFromLastModified </th> </tr>’

    SET @body = @body + @xml +'</table></body></html>’

    ———- case 2 starts
    begin

    set @message = ‘Please Do not reply on this mail’

    exec msdb..sp_send_dbmail @profile_name = ‘dba’
    , @recipients=@recipients
    , @body = @body
    , @subject= @subject
    , @body_format =’HTML’

    end

    end
    end
    end

    else print(‘bye’)

    end

  • Kindly download above 1st stored procedure from below location.

https://app.box.com/s/rntd3va60tcfb8to72oc8azd1fqy9ogj

  • Once it is done,Schedule the job on daily basis which will send the status of SQL logins.Below is the screen shot for the same.

login expiration

  • This is how you can send login expiration notification alerts in sql server.
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.