PerfMon fails writing to SQL

Question

Hi All,

(Apologies if this is in the wrong place - it's not that easy to find/select a right one!!)

I'm using Performance Monitor to collect CPU usage stats from the server.

There's no issue writing to a csv file and I have a monitor running 24/7 doing that.

However, if I select SQL for the output I get "When attempting to start the Data Collector Set the following system error ocurred: Call to SQLExecDirect failed with %1".

I can see through SSMS and a profiler trace that it creates three tables in the target database - CounterData, CounterDetails and DisplayToID.

It then runs "exec sp_tableoption 'CounterData', 'table lock on bulk load', true" and "select GUID, RunID, NumberOfRecords from DisplayToID where DisplayString = 'DataCollector01'" before logging out.

All three tables are empty.

This is running (or trying to) on Server 2016 Datacenter (10.0.14393), SQL Server 2019 CU18, ODBC Driver 17 for SQL Server (2017.177.02.01)

Any help gratefully received.

Thanks,

Paul.

Answer 1

I made it work using the driver SQL Server
(ODBC 13 and 17 didn't work)
(I didn't try SQL Server Native 11)

I think you must provide NT Authority\System the permissions to the database
if using it on the Performance Monitor side.

Answer 2

Hi @Paul Cousins

So I tried it myself and luckily I was able to regenerate the issue and fix it.

Here is what I did:
I mapped the NT AUTHORITY\SYSTEM server login to the database where I am trying to collect the information.

I then gave it permission one by one before reattempting start of Datacollector from perfmon each time:

• First I only gave it only write(db_datawriter) permission which resulted in same error and the details in the event log said cannot create table due to permission issues.
• Then I gave it ddl(db_ddladmin) permission which still resulted in same error and the details in the event log said cannot select from table (This time the table were created though, which is similar to your case).
• Then I gave it the read(db_datareader) permission after which the data collector started without issues and data in table started collecting and showing in few seconds.

Note: The errors related to PDH( one you provided in one of your comments) and different event ids are all due to some missing permissions for the user on the database.

Here is what you need to do:

• SQL Server>Security>Logins>NT AUTHORITY\SYSTEM >Right click choose properties > User Mappings
  Check the checkbox next to database where you are going to store the data coming from perfmon
  Keeping this highlighted (This is important else correct permissions don't get reflected) in the same window in below section(in database role membership section) check the boxes for db datareader ,db datawriter and db ddladmin
• To make sure you have correctly mapped this user and permissions to the database goto your database>security>users>look for NT AUTHORITY\SYSTEM (it should show there if not it was not mapped properly in previous steps) >Right click on it(if it is there) and select properties> Membership (here the permissions should show datareader, datawriter and ddladmin as checked) > If not, do it and press ok.
• Start Perfmon Data collector now (run it as system user) and it should work now.

Note2: Adding the permissions for other users from Data collector > Properties >Security tab was not getting effective for me and only SYSTEM was showing as the account with read write permission which is why I mapped NT AUTHORITY\SYSTEM itself to my test database to collect the data from perfmon.

• Even if tables are getting created it doesn't means there is permission to write /select from those tables for a user login unless you give it to him/her. (This can be tested(I myself tested too) by just giving the ddl admin permission to the user and trying to start the Data collector which in this case results in the creation of the table but then fails as it doesnt have read write permissions yet.)

In short the user needs to have ddladmin for creation of tables, ddl datawriter for writing into tables, ddl datareader to read/select from these tables as minimum permissions only after that data collector will start without throwing any errors.

Ps. The information in the window event viewer application logs was the real helping point in determining the issue for me.

• Here are some screenshots showing PDH and MSSQLSERVER as source and event id 3042, 3041 and 18456 and SELECT , CREATE , LOGON as issues at different phases during giving permissions and mapping login user etc:

I hope this will help.

Regards,
Shivam

Answer 3

Hi @Paul Cousins ,

In the ODBC system DSN configuration on the SQL Server, click the "Client Configuration" and make sure the "Dynamically determine port" is not checked

And also be sure to use Windows Authentication, in SSMS, add NT Authority\SYSTEM as a Windows login and make sure it has write access to the database you have defined in the above ODBC system DSN

You must change "Run as" for your Data Collector Set in performance monitor to your windows user which should have the corresponding rights to the database and belong to the user group

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Paul Cousins

Try running the data collector with account which has permission to read and write on sql server:

In perfmon> Right-click "Your Data Collector"-> select properties->General->Run As->choose another account which has permissions.

Or, you could give the current user full control permission:

Right-click "Your Data Collector"-> select properties->Security->Group or Users you want to give access->Permissions->Full Control

Regards,
Shivam