4

We are running Microsoft SQL Server 2014, and SQL Server Analysis Services 2014.

I am trying to setup a system to provide us with analytics concerning our SSAS OLAP cubes adoption and usage. I have setup a an Extended Event trace with the events that I need to track and it is creating a bunch of .xel files on the server like it should.

The issue I am having now is that we do not have a SQL instance on that server, so I need to load the files into a database on a different server. I can load the files if I copy them over to the SQL server and call the sys.fn_xe_file_target_read_file function with a local path, but I get an error when I try to read the file with a UNC path.

The log file name "\\SERVERNAME\f$\ExtendedEvents\MyTrace_0_12345.xel" is invalid. Verify that the file exists and that the SQL Server service account has access to it.

I have configured folder security on the SSAS server to allow the domain account that our SQL server is running under to have access to the folder. So that shouldn't be a problem. I am making the assumption that this function does not like UNC paths, so my next step would be to map a network drive on the SQL server machine so I can pass a local path to this function.

Before I request this from our DBAs I wanted to reach out to see if my assumption about the UNC paths is correct, and if any one else has worked around a similar issue by mapping a network drive to the remote share. I have read through the MSDN documentation for the function, but I cannot see anything to indicate how I would read a file on a network share.

  • 1
    2 things come to mind - 1. - the network path should be \\servername (see 2 back slashes) and 2. the folder ExtendedEvents if it is shared, you should not need f$. try just running exec xp_cmdshell 'net use \\SERVERNAME\ExtendedEvents /USER:domain\UserAccount_or_ServiceAccount str0ngP@ssw0rd' – Kin Shah Dec 30 '16 at 20:58
  • Thank you @Kin, I edited my question to show the 2 slashes (the blockquote markup removed one of them) The issue was with my share not having the correct access. – Brandon McClure Jan 3 '17 at 15:04
6

As Kin suggested in his comment, the F$ share is problematic since that only allows administrator access. Although you've provided file-system access to the folders in question, the account does not have access to the share. Create a share specifically for the xel files, and provide access to the SQL Server Service account on both the Share, and the folder.

Using the Universal Naming Convention name, or UNC, is preferable to accessing the file via a mapped-network-drive. A mapped network drive just obfuscates the true location of the file, and you'll need to somehow create the mapping inside the profile for the SQL Server Service account (that's what Kin is suggesting with the net use command he shows in his comment.)

My SQL Server 2012 instance reads files across the network perfectly like this:

SELECT *
FROM sys.fn_xe_file_target_read_file(
    '\\machine\share\ErrorLogs\system_health_*.xel' --this is the UNC
    , null
    , null
    , null
    );
| improve this answer | |
  • 2
    Thank you @MaxVernon, it was an issue with the share security. I created a share to the folder that the xel files are in and allowed the service account access to the share. With that new share I was able to access the files. – Brandon McClure Jan 3 '17 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.