Reading Transaction Log

Someone or a service deleted a record from a table. We will need to know when it was deleted and who deleted the record. I read every transaction log between a specific time to find out if there was a delete statement in transaction log.  Transaction logs are located on E:\

Reading Transaction Log

Option 1

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID],LEFT ([Description], 40) AS [Description]  FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE Operation = 'LOP_DELETE_ROWS'

GO

Option 2

In the script below, we passed table name and operation

WITH CTE

as

       (SELECT [Transaction ID], count(*) as DeletedRows

       FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

       WHERE Operation = ('LOP_DELETE_ROWS')

       AND [PartitionId] = (SELECT sp.partition_id

                            FROM sys.objects so

                            INNER JOIN sys.partitions sp on so.object_id = sp.object_id

                            WHERE name = 'TESTTABLE')

       GROUP BY [Transaction ID]

       )

SELECT [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as LoginName, DeletedRows

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a

INNER JOIN cte on a.[Transaction ID] = cte.[Transaction ID]

WHERE Operation = ('LOP_BEGIN_XACT')

Option 3

If we know specific transaction SID, we can find who ran the delete.

SELECT

    [Current LSN],

    [Operation],

    [Transaction ID],

    [Begin Time],

[End Time],

[Transaction Name],

LEFT ([Description], 40) AS [Description],

SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?],

[PartitionID],

[Num Elements] ,

    [RowLog Contents 0],

    [RowLog Contents 1],

    [RowLog Contents 2],

    [RowLog Contents 3]

FROM

     fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE [Transaction ID] in('0000:0084ff66','0000:0084ff66','0000:0085002c')

GO

I used above three queries to find out who deleted a record and delete time.

Thank you for reading.

Create SQL Login in SQL AlwaysOn availability group servers

How to create SQL login in SQL AlwaysOn Availability group databases

First create SQL Login in Primary replica
Example : Login : TESTLOGIN, Password : test, Database : DBA, db_datareader

Find SID of SQL Login in Primary replica

Run the following query in primary SELECT SUSER_SID ('TESTLOGIN') to find SID of the SQL login in Primary replica
Copy the query result which is SID, Example : 0x4063F34B8AF7A041B66CF243AAC79ECC

Create the same SQL Login in Secondary node using the script below. Paste the SID from primary node in SID area, password in passsword.
In the script below, update the areas LOGIN, PASSWORD, SID, DEFAULT_DATABASE

USE [master]

GO

CREATE LOGIN [TESTLOGIN] WITH PASSWORD=N'test' 

,SID=0x4063F34B8AF7A041B66CF243AAC79ECC, DEFAULT_DATABASE=DBA,

DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

After updating the query, Run the query in Secondary node

We use the same SID for SQL login in order to work after failover happens.

Thank you for reading.

A database in Availability Group secondary node (Not Synchronizing/In Recovery) Status

Tempdb filled up last night and databases availability group are not accessible in secondary replica. After restarting a secondary SQL instance, one of the databases in Not Synchronizing / Recovery Pending mode.

I searched the issue on google and apply a resolution from

http://dba.stackexchange.com/questions/129471/availability-group-database-stuck-in-not-synchronizing-recovery-pending-mode

1. Suspend data movement of the database in secondary

ALTER DATABASE [DB1] SET HADR SUSPEND;

GO

2 . Remove database from AG group


Alter Database [DB1] SET HADR OFF;

GO

3. Restore the latest transaction logs (before tempdb is filled up/databases are not accessible) in secondary node. All the transaction logs after that.

RESTORE LOG [DB1] FROM DISK = '\\backups\DB.trn' WITH NORECOVERY;

GO

4. Re-join database to availability group

ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [SENetwork_AG];

GO

5. Resume the data movement

ALTER DATABASE [DB1] SET HADR RESUME;

GO

The above solution works for me. The database becomes Synchronized status.

Thank you for reading

Fix : Message Executed as user: Proxy Domain account. The process could not be created for step 1 of job 0xD656A2765BDCF54F91F7D2CA16398CC3 (reason: A required privilege is not held by the client). The step failed.

I am running a job on SQL 2016 and server is windows 2012 R2. The job is run as proxy account which is domain account. In the job step: Type is Operating System(CmdExec), Command is Powershell as an example below.

PowerShell.exe "E:\PowerShell\FindLastRebootDate\ServerLastReboot.ps1"

The job failed with the error below.

Message Executed as user: Proxy Domain account. The process could not be created for step 1 of job 0xD656A2765BDCF54F91F7D2CA16398CC3 (reason: A required privilege is not held by the client).  The step failed.

I was able to fix the issue by.

Step 1. Change the service account running SQL Agent to Local service and restarted the SQL Agent
Step 2. Change the local service running SQL Agent to the previous service account back and restarted the SQL Agent

After that I run the job, the job ran successfully.

Thank you
Mya

Checking Server online Status - PowerShell

We managed 100 + servers and we want make sure they come back after monthly maintenance reboot. We will run the following script by confirming the servers are Up. I retrieve servername from a Table called SQLServers from Database - DBA_ServerDW from ServerInstance name SQLTEST.

$servername = invoke-sqlcmd -ServerInstance SQLTEST -Database DBA_ServerDW `
-Query "select ServerName from DBA_ServerDW.dbo.SQLServers
where servername not like '%\%'"
ForEach ($server in $servername)
{
   # Ping the machine to see if it's on the network
   $ServerN=$server.ServerName
   $results = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$ServerN'"
   $responds = $false  
   ForEach($result in $results) {
      # If the machine responds break out of the result loop and indicate success
      if ($result.statuscode -eq 0) {
         $responds = $true
         break
      }
   }
         If ($responds) {
      # Gather info from the server because it responds
      Write-Output "$ServerN responds"
   } else {
      # Let the user know we couldn't connect to the server
      Write-Output "$ServerN does not respond"
   }
}

Tempdb.mdf file takes on Model database initial size

I changed model database size  to 5 Gbs so new user databases will take on Model database size.

USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 5242880KB )
GO

Before restart SQL instance, I have sized tempdb datafiles size to 512 MB

Then I restarted SQL instance and agent. It takes a while to connect to SQL server via SSMS.

Tempdb.mdf file is recreated with 5 Gbs which takes on Model database initial size.

According to MS KB, tempdb should not take on Modeldb.
https://support.microsoft.com/en-us/kb/307487

PowerShell - Find AlwaysOn Primary node Report

I was working on a powershell script which tells me which node is AlwaysOn primary replica. We have 38 servers which participate in AlwaysOn availability group technology. This script will help you to determine which server is a primary replica without logging into each server and find out.

NOTE:

I set up a path which is for error log at E:\PowerShell\GetAlwaysOnPrimary\

DBATestServer is our DBA server and DBA_ServerDW is DBA inventory database

SQLServers table is in DBA_ServerDW database. The table is already populated with a list of servers that we manage in our organization.

First, I will get a list of server names from SQLServers table. Then each server will be passed in for loop. I use invoke-sqlcmd command to all sql statements. Inside for each loop , I will get each server info if it is primary replica. Finally, I will omit all null results from the query and insert only returned data to a table AlwaysOnPrimary in DBA_ServerDW database. Thank you for reading my blog. Enjoy scripting!

$($errorlog = 'E:\PowerShell\GetAlwaysOnPrimary\Error.txt'

Clear-Content $errorlog

#Get Server List from SQL server inventory table, exclude name instances

$servername = invoke-sqlcmd -ServerInstance DBATestServer -Database DBA_ServerDW `

-Query "select servername from SQLServers

where SQLservers.Purpose like '%AlwaysOn%'"

foreach($server in $servername){

Try{

#convert system.object data type to String

$ServerN=$server.ServerName

$AG = invoke-sqlcmd -ServerInstance $ServerN -Database master `

                -Query "IF SERVERPROPERTY ('IsHadrEnabled') = 1

BEGIN

SELECT

  RCS.replica_server_name as ServerName

 ,AGC.name as AvailablityGroupName

  , ARS.role_desc as Role

 , AGL.dns_name as ListenerName

FROM

 sys.availability_groups_cluster AS AGC

  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS

   ON

    RCS.group_id = AGC.group_id

  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS

   ON

    ARS.replica_id = RCS.replica_id

  INNER JOIN sys.availability_group_listeners AS AGL

   ON

    AGL.group_id = ARS.group_id

WHERE

 ARS.role_desc = 'PRIMARY'

END"

$SName = $AG.ServerName

$GName = $AG.AvailablityGroupName

$Role  = $AG.Role

$LN    = $AG.ListenerName

if ($SName)

        {

                invoke-sqlcmd -ServerInstance DBATestServer -Database DBA_ServerDW `

                -Query  "INSERT INTO AlwaysOnPrimary(ServerName,AvailabilityGroupName,Role,ListenerName)

                        VALUES ('$SName','$GName','$Role','$LN')"

          }

}

Catch

{    Clear-Content $errorlog

     "Fail to get information $ServerN +' ' + $RunTime :$_" |Out-File $errorlog -Append

}

}

Add a database to AlwaysOn availability group automatically

Description: The following script adds new databases to AlwaysOn Availability Group. You can create it as a stored procedure.
To add a database to availability group automatically, you will need to create a server level trigger or a new database creation alert. Then, you call the script as a stored procedure from the server trigger or the database creation alert. I do not include server trigger on this page. You can find it on google. Thank you for reading!

NOTE : Before you run the stored procedure, please create a linked server between primary and secondary replica on both replica. Set RPC and RPC Out to True on the linked server properties

=============================================

DECLARE @linkedserver as SYSNAME
DECLARE @sql1 NVARCHAR(MAX)
DECLARE @sql2 NVARCHAR(MAX)
DECLARE @path VARCHAR(150)
DECLARE @bakupfile VARCHAR(200)
DECLARE @db VARCHAR(255)
DECLARE @BackUpsql NVARCHAR(MAX)

--Get a linked server information
SET @linkedserver = (select Name from sys.servers where server_id <> 0 and is_linked = 1)

-- Get back up path from database setting
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',  
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name = 'BackupDirectory', @path = @path OUTPUT ;  

--Added back slash at the end of back up path
Set @path = @path + '\'

DECLARE AG_cr CURSOR LOCAL FOR

 -- Get new databases which are not part of an availability replica of in availablity group

  SELECT name FROM sys.databases 
  WHERE replica_id IS NULL 
  AND database_id > 4 and 
  source_database_id is NULL 
  and State =0 and 
  name not in('distribution','ReportServer','ReportServerTempDB')

  OPEN AG_cr

  FETCH AG_cr INTO
  @db

WHILE @@FETCH_STATUS >= 0
        BEGIN

print @db
--Change database recovery mode to FULL

  IF (SELECT recovery_model FROM sys.databases WHERE name=@db) <> 1
        BEGIN
            PRINT 'Changing recovery model to FULL';
            DECLARE @ModeChange NVARCHAR(512) = 'ALTER DATABASE [' + @db + ']  SET RECOVERY FULL WITH NO_WAIT';
            EXEC sp_executesql @ModeChange;
        END;
    ELSE
        BEGIN
            PRINT 'Database is already in FULL recovery mode.'
        END;

Print 'Initial Database BackUp...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP DATABASE ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '.BAK'';'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;


Print 'Backing up a database...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP DATABASE ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '_AG.BAK'' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5;'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;

Print 'Backing up a Log...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP LOG ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '_AGLOG.TRN'' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5;'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;

set @bakupfile = @db +'_AG.BAK'

set @sql1 = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N'' RESTORE DATABASE ' +@db+ ' FROM DISK ='+''''''+ @path + @bakupfile+''''''+ 'with norecovery'''

print @sql1

Exec sp_executeSQL @sql1

set @bakupfile = @db +'_AGLOG.TRN'

set @sql1 = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N'' RESTORE LOG ' +@db+ ' FROM DISK ='+''''''+ @path + @bakupfile+''''''+ 'with norecovery'''

print @sql1

Exec sp_executeSQL @sql1


--join AG group
DECLARE @AGroupName VARCHAR(200)
SET @AGroupName= (select Name from sys.availability_groups)


PRINT 'Joining database to availability group...'

DECLARE @AddDbToAG NVARCHAR(2048) = 'USE master' + char(13)+'ALTER AVAILABILITY GROUP [' + @AGroupName + '] ADD DATABASE [' + @db + ']';

EXEC sp_executesql @AddDbToAG;

PRINT 'Bringing replica online'     

DECLARE @AddHADR NVARCHAR(2048) = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N''ALTER DATABASE [' + @db + '] SET HADR AVAILABILITY GROUP = [' + @AGroupName +'];''';

EXEC sp_executesql @AddHADR


FETCH AG_cr INTO
  @db

END

 CLOSE AG_cr;
 DEALLOCATE AG_cr;

Changing User Database Owner - sp_MSforeachdb

Declare @Command varchar(max)
Select @Command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false END'
Print @Command
EXEC sp_MSforeachdb @Command

GO

Poor Performance Query SQL Trace Profiler

SQL Trace causes overhead to the server which is experiencing performance issue. When do we need to use SQL trace? Which template to choose to trace poor performance query? 

RPC:Completed
    
SP:StmtCompleted
    
SQL:BatchStarting
    
SQL:BatchCompleted
    
Showplan XML

Credit goes to- http://blogs.msdn.com/b/dstfs/archive/2016/01/05/how-to-troubleshoot-slow-performance-issue-in-team-foundation-server-using-sql-profiler.aspx

Fix - 'dbproviderfactories' section can only appear once per config file'. SQLPW is not installed

We tried to create a new data source in a brand new Visual Studio reporting service solution 2012. We got an error prompt.

SQLPW is not installed

'dbproviderfactories' section can only appear once per config file'.

After we clicked Ok button on the prompt, a data source is created without connection string. We cannot update the data source in connection string. Edit button next to the text field for the connection string is disabled.

I did some research on google and found that the error is due to a corrupted version of machine.config which is related to Dot Net Framework.

machine.config is located in two places and they are 32 and 64 bit version. On my computer, they are located in 

64 bit

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config

32 bit

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config 

The file is corrupted due to an extra tag highlighted in yellow.To fix the error, we will need to delete the extra tag.

Step 1 : Back up machine.config file in both location(Copy the file and paste in the same location, Machine- Copy.config will be created.

Step 2: Open a notepad as an administrator and open  machine.config file from 32 bit location

Step 3: Find dbproviderfactories

Step 4. Remove the extra tag and save it.

Repeat step 2 to 4 for machine.config 64 bit version.

Close your visual studio reporting soultion. Reopen it again. In my case, Edit button is working now.

Thank you for reading. Have a nice day!

Dynamic DataSource in SSRS report

I ended 2015 in a good way by passing 70-462 Microsoft certification.

Hello 2016 ! 

I am writing the first blog of 2016. I wish Everyone Happy New Year. May this year bring you joy,peace,good health and prosperous.

I want to blog about how to set up a dynamic datasource in SSRS reports. You might have already known how to set it up. Hope my blog comes in handy when you need to recall how to do it.

In this example, I am creating SSRS Execution Log Report. We have three different datasources (Dev/QA/PROD)- SSRS reportserverdb servers. I have created a report which queries SSRS executionlog3 view from reportserverdb. In my case, I am connecting different datasource one at a time. In order to dynamically connect to each datasource upon choosing a server name from a drop down box, I will need to create a dynamic datasource.

The report below is already created. I want to show you how you can set up dynamic datasource.

Under Data Source, Create a datasource which is named DDS in my case. DDS is my dynamic datasource which will connect to different server upon request (drop down server name choice). 

Open DDS datasource by double clicking it. Choose Embedded connection and open expression (fx)

In expression, enter   ="Data Source=" & Parameters!ServerName.Value

Under credential, choose to use windows authentication

I have two datasets in my report - Serverlist and Executionlog

Under Datasets, Serverlist dataset will get servername from a table called SQLServers. (SQLServers table should be created and populated with data(server names)before this report)

ExecutionLog Dataset has a query which retrieves ssrs executionlog from report server.

I used the below query in Executionlog dataset

use ReportServer

SELECT  

InstanceName,

UserName,

Itempath,

ItemAction,

TimeStart,

TimeEnd,

DateDiff(MILLISECOND,TimeStart,TimeEnd) TimeTakenToggle,

TimeDataRetrieval + TimeProcessing + TimeRendering as [total time_ms],

TimeDataRetrieval TimetoRetrieveAllDataset, 

        TimeProcessing TimeSpentInProcessEngine,

TimeRendering TimeSpentOnReportingService,

ByteCount NoOfBytes,

[RowCount],

AdditionalInfo,

CHARINDEX('\', Instancename) Getposition,

Substring(InstanceName,1,CHARINDEX('\', Instancename)-1) ServerName,

'\\'+Substring(InstanceName,1,CHARINDEX('\', Instancename)-1)+'\e$\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles' Logfilelink

FROM ExecutionLog3(nolock)

where convert(varchar(10),timestart,101) >= @DateStart and 

convert(varchar(10),timestart,101) <= @DateEnd

ORDER BY Timestart DESC

When I preview the report, I will have my SSRS reportserverdb server list. I can choose one server and choose datestart and dateend (which I set it up in parameters)

Preview did not give errors. I am going to deploy the report in SSRS reporting service.

After deploying the report to reporting service, I will need to configure permission for this report in reporting service. For example, I am using domain credential MYDOMAIN\SSRSEA and it's password to connect to each servers. MYDOMAIN\SSRSEA account must be added to each SSRS server as db reader in reportserverdb. To use MYDOMAIN\SSRSEA to connect to each server, we will need to configure as below.

In username : MYDOMAIN\SSRSEA and enter it's password accordingly.

NOTE : Check Use as windows credential when connecting to the data source 

Click Apply at the bottom of the page.

Now, you will be able to view the report and it is working!