Analysis Services – Error – OLE DB Error : OLE DB or ODBC Error : Login failed for user

 

Background

Trying to build an Analysis Services’s Cube, but getting the error pasted below:

 

Error Message

Image

OLEDBOrODBCErrorFailed-20170309-1256PM [BrushedUp]

 

Textual

 

OLE DB error : OLE DB or ODBC Error : Login failed for user

A connection could not be made to the data source with the DataSourceID of ‘LocalHost’, Name of ‘LocalHost’

 

TroubleShooting

 

Analysis Server

Database

Data Source Properties

Image

DataSourceProperties-BIS-20170309-0124PM (Brushed-up)

 

Explanation
  1. Connections
    • Driver :- SQL Server Native Client
    • Data Source :- .
      • . means localhost
    • Authentication
      • Integrated Security=SSPI
    • Database
      • Initial Catalog
        • BIS
  2. Maximum Number of Connections is set to 10
  3. Isolation Level is Read Committed
  4. Impersonation is set to ImpersonateServiceAccount

SQL Server Error Log

Review SQL Server Error Log File

We have two sets of error.

The first set was before we granted SQL Server Instance privilege.

And, the next was after we granted SQL Server Instance privilege, but had yet to grant targeted database privileges.

Image

Could not find a login matching the name provided / Error 18456

 errorLog_20170308_0633PM_to_0635PM (BrushedUp)

 

Login failed for user ‘NT Service\MSSQLServerOLAPService’. Reason failed to open the explicit specified database / Error 18456 – Severity 14: State 38

errorLog_20170309_0118PM_to_0125PM (Brushed up)

Tabulate

 

Error Category Section  Message
 Login Not Granted SQL Login
Login Login failed for user ‘LAB\DBSQL$’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Login  Error: 18456, Severity: 14, State: 5.
Login Not granted DB Privilege
 Login Login failed for user ‘NT Service\MSSQLServerOLAPService’. Reason: Failed to open the explicitly specified database ‘BIS’. [CLIENT: <local machine>]
 Login  Error: 18456, Severity: 14, State: 38.

 

 

Remediation

Grant Access to SQL Instance

Grant SQL Instance, Database Connection privileges to account that Analysis Service is running under.

Login

Local Machine

 
create login [NT Service\MSSQLServerOLAPService] from windows;
 

Grant Access to Individual Database

Local Machine

 
use [--database--]
go

create user [NT Service\MSSQLServerOLAPService] from login [NT Service\MSSQLServerOLAPService];
go
 

Grant Object Permission

Local Machine

 
use [--database--]
go

exec sp_addrolemember 'db_datareader',  [NT Service\MSSQLServerOLAPService];

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s