Thursday, August 16, 2012 10:04 PM
I need to enable logging for usage based optimization. I've set all the properties on the SSAS Server, but get nothing when running queries in Production.
Log \ Query log \ CreateQueryLog Table = true
Log \ Query log \ QueryLogConnection String = [my connection string]
Log \ Query log \ QueryLogSampling = 10
Log \ Query log \ QueryLog TableName = OLAPQueryLog
Is there a setting I'm missing somewhere? Could it possibly be a permissions issue with the service account?
- Edited by jschroeder Thursday, August 16, 2012 10:22 PM
Friday, August 17, 2012 5:45 AM
Check whether the OLAPQueryLog was created, and if yes go back to the properties and set the CreateQueryLogTable to false.
Friday, August 17, 2012 1:17 PM
Follow the below steps
1. Set CreateQueryLogTable to true
2. Set the value of QueryLogConnectionString appropriately
3. Set the value of QueryLogSampling to the appropriate value for your environment
4. Set the value of QueryLogTableName appropriately.
5. Click "OK"
Check out the below link for details,
Friday, August 17, 2012 3:11 PMI already did all this before posting and still get nothing. The table is never created.
Friday, August 17, 2012 6:07 PM
The DBA found this error message in the SQL log:
The description for Event ID '-1054801917' in Source 'MSSQLServerOLAPService' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'OLE DB error: OLE DB or ODBC error: CREATE TABLE permission denied in database ['MyDatabase'].; 42000.'
- Edited by jschroeder Friday, August 17, 2012 6:12 PM
Friday, August 17, 2012 6:34 PM
So, it's a permissions issue as the account under which SSAS instance is connecting to your DB has no permissions to create a table in the database.
You have 2 options.
- If you use windows authentication in the connection string to the log database, you need to grant appropriate rights to the account under which the SSAS instance is running to the log database.
- Create a SQL User, grant him appropriate rights to the SSAS database and use that SQL user as SQL Authentication in the Log Database connection string.
The account needs:
- CREATE TABLE permission in the LOG database (at least for the time when the log table is being created - when you first time confirm the options dialog with CreateQueryLogTable = true
- INSERT permission to the table after it is being created to be able to insert the log records.
If you have a separate database for the OLAP Query Log, you can make the account a member of the db_owner database role. Then it will have a complete control over that db and as the db will be used only for the OLAP Query Log, then there is also no security issue.
Friday, August 17, 2012 6:47 PMThe service account SSAS runs under has all the necessary permissions, but the create table is still being denied. We have not tried SQL authentication yet.
Friday, August 17, 2012 8:18 PMSeems, that it doesn't have all the necessary rights based on the error message. BTW, the SQL Server Instance is a Local Instance, or is running on another machine? Also The Service account is a Local Account or Domain Account?
- Marked As Answer by jschroeder Monday, August 20, 2012 2:26 PM
Wednesday, January 09, 2013 8:00 PM
Thanks, Pavel. That is what I needed. Seems SSAS was installed with Default Service Accounts that had no permissions. From the Services app, I changed the LogOnAs to my own account (which has dbCreator permissions) and restarted SSAS. From Management Studio, changing "CreateLogQueryTable" to "true" created the table.