MDW: The data collector cannot connect to the management data warehouse Login failed for user DomainName ServerName in SQL Server
- Details
- Written by Ranjeet
Recently I configured Management data warehouse and setup the data collector. I chose two SQL instances to separate MDW database and Data collection jobs to target/production server. When everything done in setup without any error, I was expecting to start collecting data but encountered failing data collection job with below error.
Message
Executed as user: NT Service\SQLAgent$ServerName01. SSIS error. Component name: GenerateTSQLPackageTask, Code: -11111111, Subcomponent: Generate T-SQL Package Task, Description: An error occurred with the following error message: "An error occurred while verifying the result set schema against the output table schema. The data collector cannot connect to the management data warehouse. :
Login failed for user 'DomainName\ServerName$'.". . SSIS error. Component name: GenerateTSQLPackageTask, Code: -111111111, Subcomponent: Generate T-SQL Package Task, Description: An error occurred with the following error message: "An error occurred while verifying the result set schema against the output table schema.
The data collector cannot connect to the management data warehouse. : Login failed for user 'DomainName\ServerName$'.". . The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.
Solution: to fix this issue you need to go to the MDW instance and add the above login to allow access MDW. Below is the steps.
SQL Server: SQL Server blocked access to STATEMENT OpenRowset OpenDatasource of component Ad Hoc Distributed Queries because this component is turned off as part of the security configuration for this server in SQL SERVER
- Details
- Written by Ranjeet
I was trying to run SQL query on remote instance by using OPENROWSET and it throw below error. By reading error itself, we can understand what is the next step. It saying you need to enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure.
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Solution: we can use below query to enable this.
sp_configure 'show advanced options', 1;
reconfigure;
go
sp_configure 'ad Hoc Distributed Queries', 1;
reconfigure;
GO
And to disable
sp_configure 'show advanced options', 0;
reconfigure;
go
sp_configure 'ad Hoc Distributed Queries', 0;
reconfigure;
GO
SSRS: Query execution failed for dataset dstReportSummary. rsErrorExecutingCommand in SSRS
- Details
- Written by Ranjeet
While running a SSRS report I encountered below error. Actually I restored new copy of production copy to development box and changed the connection string pointing to new database. when I ran the report after deploying new changes. It thrown the error. Initially I checked the data source and updated its credential but didn’t work.
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'dstReportSummary'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Solution: I went back and checked the user database mapping and its privileges. Instead of db_datareader role I had assigned db_datawriter role. I just revoked db_datawriter privilege and assigned db_datareader role and error disappeared.
SSRS: Report Data items in new Report pan in SSRS
- Details
- Written by Ranjeet
I just finished on important article series which was covering different commonly used features of Table report item. In this article I will try to list out the report data option of new report pan. When you create a new report you get “Report Data” table in left hand side. Each of the “Report Data” item is important to understand then only you can actually be good report developer. Let me list out those items. In next few articles I will show how and when to use these.
- Built-In-fields
- Parameters
- Data Sources
- Datasets
SQL Server: The EXECUTE permission was denied on the object sp_notify_operator, database msdb, schema dbo in SQL SERVER
- Details
- Written by Ranjeet
Recently I automated some SQL job process which was doing some transaction in the database and then sending the report email to concerned users. Once I deployed it to respective environment, I added the users to that database and given read-write permission. When user tried to test it, it thrown the below error.
Msg 229, Level 14, State 5, Server InstanceName, Procedure sp_notify_operator, Line 1 The EXECUTE permission was denied on the object 'sp_notify_operator', database 'msdb', schema 'dbo'.
By reading above error, it is pretty clear that particular user doesn’t have access to execute sp_notify_operator stored procedure in msdb database. So, the next step is to provide user the execute permission. There is DatabasemailUserRole role in msdb which holds sufficient privileges to send emails. Below is the syntax I used.
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = 'DomainName\UserName'
SECURITY: How to grant shared location access to sa account in SQL SERVER
- Details
- Written by Ranjeet
In day to day DBA activity, you always come to know about few things which usually sometime you won’t concentrate on it. Today, I got a customer request asking that sa doesn’t have permission on file system. Actually user were trying to read/write some file on some file share location. Question is how will you give a permission for read and write to a user/service account. Something to think on sql server level. It is the SQL server service which actually go and read/write the file. On the same basis I thought to work by this way and it worked. Below is the steps I performed.
- Go to the file share location
- Right click on the folder in which file is to be read/write
- Click Properties and go to security tab
- Click Edit and then Add
- Give your server account name
- Open services.msc
- Right click on SQL Server service
- Go to logon tab
- Copy “this account” value
ALWAYS ON: Command to get Always On network setup configuration details in SQL Server
- Details
- Written by Ranjeet
Recently I setup always on availability group with listener. We are facing some time out issue when doing fail over. I will be discussing more on this issue later on some other article. Here I am just want to put two command line instruction you can use to get IP and other network details related which helps in trouble shooting. You can run this command in PowerShell command line.
- ipconfig /all >ipconfig_IP_Details_%computername%.txt
- Get-ClusterResource|Get-ClusterParameter |fl |out-file ClusterResourceDetails.txt
I am not listing its output here but you can run on your test server to get result.
SSRS: How to rename column header in table report item in SSRS
- Details
- Written by Ranjeet
It is very common operation we do while designing new report. When you drag the column from dataset to table, column updated the header with default column name. In most of the cases we don’t keep the exactly same name as column does have but in few yes. In this article, I am going to demonstrate how to rename column header caption. It is pretty common to do as we do for other in Microsoft technology. Below is the two method you can rename.
- Click on column header twice with little interval and
- Then edit it
There is no option to right click and then rename. Know about other frequently used table feathers.
SSRS: How to add report item table level filter features SSRS
- Details
- Written by Ranjeet
We already have discussed about different features of table report item. Here in this articles I am going to demonstrate to add filter in report table item. When we talk about data filter it means same as putting where clause in SQL query. Here datasets has already been designed and fetching the data and we don’t want to put any additional filter in that datasets. One reason would be, other report might using same datasets. So, we will have to put filter on table level itself. Okay, so let see the steps below to do the same.
- Right click on top left corner of the table and select “Tablix Properties”
- Once Tablix properties dialog box appeared, and go to “Filters” tab
SSRS: How to provide column level interactive features in table report item in SSRS
- Details
- Written by Ranjeet
We already have discussed about different features of table report item. Here in this articles I am going to demonstrate how to add column level interactive sorting features. When you explore the tabular report you some time feel that here on report only it would be great if I can sort on date instead of name or using some other column. SSRS has given this features and you can enable in just few clicks. Let me go through those steps here.
- Click on column header on which you have to add interactive features
- Right click on it and select “Text Box Properties”
- You will get below “Text Box Properties” dialog box
More Articles...
- SSRS The definition of the report report name is invalid. The FilterExpression expression for the dataset dtsMatchUnmatch refers to the field FieldName. Report item
- SSRS: How to do table header formatting in SSRS
- SSRS: How to set table level shorting order in SSRS
- SSRS: How to insert new columns or merge two cells in table report item in SSRS
- SSRS: How to assign datasets field into table columns in SSRS