SSAS Data Source Impersonation Information
-
Good Moring,
Currently I am writing my Bachelor Thesis. As part of this, I have to set up a Data Warehouse. Due to I use SSIS to transfom Flat-files into a SQL Server 2017 database. In the next step I want to use them as data source for an Multidimensional Cube. Therefore I want to use SSAS. There is only one Problem: I can't connect to the noticed SQL Server 2017 database.
When I test the connection it works, but when I try to deploy or even process it an error occurs. The System can not connect to the database. No matter which of the for Option of Impersonation Information i have tried, nothing worked. I also have turned of the Firewall to test if it blocks anything, but no Change.
I really hope that somebody can help me.Kind Regards
- tokoc - Thursday, August 24, 2017 2:20 AM
We probably need some more information to help you out. A few questions would be:
What is the piece that can't connect to the database - are you using Visual Studio, SQL Server Data Tools?
What are the errors you get when you try to deploy or process a cube?
Are you doing all of this on your own PC where everything is installed on your own computer or are you trying to connect to a server that has Analysis Services from your PC?Sue
-
Thanks a lot for the prombt response.
Yes, I use SQL Server Data Tools for Visual Studio. I have tried 2013 and 2015 already.I guess the error reports won't help that much, since they are german.
The german error is the following: Die Output_DS-Datenquelle enthält einen ImpersonationMode-Wert, der bei Verarbeitungsvorgängen nicht unterstützt wird.
In English it should be something like:datasource, Outpus_DS, contains an ImpersonationMode that is not supported for processing operations.
But I note that this error only occurs, when i use the credentials of the current user.When I use Inherit, default, the service account or the local Administrator the following occurs:
German: OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Anmeldungstimeout abgelaufen; HYT00; Netzwerkbezogener oder instanzspezifischer Fehler beim Herstellen einer Verbindung mit SQL Server. Der Server wurde nicht gefunden, oder auf ihn kann nicht zugegriffen werden. Überprüfen Sie, ob der Instanzname richtig ist und ob SQL Server Remoteverbindungen zulässt. Weitere Informationen erhalten Sie in der SQL Server-Onlinedokumentation.; 08001; SQL Server-Netzwerkschnittstellen: Die angegebene LocalDB-Instanz ist nicht vorhanden.[x89C50107]. ; 08001.
English translation: OLE DB error: OLE DB or ODBC error: Connection timeout; HYT00; Error regarding to network or instance during connection to the SQL Server. Server not found or accessible. Check the instancename and if the SQL Server allows remote connections. Further Information in the SQL Server Online documentation.; 08001; SQL Server interface: Local-DB instance not found [x89C50107].; 08001.Everything runs local on my PC. I think the orign of the problem is the connection of the data source to the SQL Server database. When I connect to the Analysis Server in the SSMS everything seems to be there, but I can't access anything, so I think it deploys but do not process.
If you need any more information feel free to ask.
Tobi
-
Thanks for posting those. The error messages do help a lot as they can indicate what the problem is.
On one of those errors, it was looking for Local-DB. Is that what you wanted to use for the data source?
It sounds like that may not be correct. Did you install a regular version of SQL Server 2017? Can you try changing the connections string to the datasource to use: (local)
When you hit Edit under the connection string when you are viewing the datasource, put in (local) for Server and see if that works.And the first error in English is : ImpersonationMode that is not supported for processing operations.
Not sure about 2017 but on the other versions, you would get that error if using Current User for the DataSource impersonation information. Refer to the Options section in this article which shows the two that can be used:
Impersonation (SSAS Tabular)Sue
-
Thanks for the ideas! The connection string should be correct i guess. Since it connects when i click "test connection" and for the SSIS part of the project it is no problem to connect too.
As far as i know i installed the regular version of SQL Server 17.1.I have tried the explained by you link: I have tried <Domain name>\Administrator and the belonging passwort. But again the 2nd mentiond error. The same error occurs, when I use the Service Account.
Do you have any other ideas?
Tobi
- tokoc - Monday, August 28, 2017 12:35 AM
SQL Server 17.1 is likely just the version of SQL Server Management Studio you have installed.
If it's the second error than it is likely the connection string used. So it goes back to what SQL Server database engines you have installed - Express, local db, developer edition? You may have a few different ones along those lines. It could be that where it's the point to the wrong database instance or it could be that the instance is not running.
I see where you said you connect to Analysis Services fine with SSMS - Do you connect to any Database Engines in SSMS?
Have you looked in the Services to see what database engines are there?
Look at what you used in the connection string and that should tell you where it is pointed to. And check and see if that is running and check and see if you can connect to it using SSMS.Sue
-
As far as I know, I have installed the developer edition. Is there a way to check this? And which is the right to use?
Yes, I can connect to the Database Engines in SSMS without any problems.
My connection string is right here: "Provider=SQLNCLI11.1;Data Source=(localDb)\LocalSqlDW;Integrated Security=SSPI;Initial Catalog=Output", but this should be the problem since I am able to connect, when I hit the "test connection"-Button, shouldn't it?I think the following Services are the only one, related to the SQL Database and around it. Do they help you?
Name PID Discription Status Group
SQLBrowser 3108 SQL Server-Browser Running
SQLTELEMETRY 5012 SQL Server CEIP service (MSSQLSERVER) Running
SQLWriter 2664 SQL Server VSS Writer Running
SSDPSRV 1720 SSDP-Suche Running LocalServiceAndNoImpersonation
SSISTELEMETRY130 4764 SQL Server Integration Services CEIP service 13.0 Running -
try the following:
1. On your Data Source Designer, Click 'Impersonation Information' Tab, select the 'Use the service account' Option.
2. On 'General' tab, Click "Edit" button, Try to use SQL SERVER Authentication option.
3. Click Test Connection button. - tokoc - Tuesday, August 29, 2017 12:13 AM
No..I don't see SQL Server in there or what would be in there if you had installed SQL Server Developer. But, you must have it somewhere if you have installed analysis services and are connecting to something.
When you connect with SSMS, what is the Server Name you connect to?
Run the following in SSMS - the results should give you the information you need on what server, edition, instance:SELECT
SERVERPROPERTY('MachineName') as MachineName,
SERVERPROPERTY ('InstanceName') as InstanceName,
SERVERPROPERTY('Edition') as Edition,
SERVERPROPERTY('IsLocalDB') as IsLocalDBOn the services, make sure to look at all services, not just the running ones. Another area on the different versions of things installed, Open up Control Panel, go to Programs and Features. Look or Microsoft SQL....in the applications list.
Sue
-
When I connect to SSMS I use (localDb)\LocalSqlDW as Servername. That's a local created Server.
The results of your query are the following.MachineName: KOCH-T-NB
InstanceName:LOCALDB#4CB18A34
Edition:Express Edition (64-bit)
IsLocalDB: 1
Here are some more services including the closed ones.
Name PID Discription Status
MSSQLFDLauncher SQL Full-text Filter Daemon Launcher (MSSQLSERVER) Closed
MSSQLSERVER SQL Server (MSSQLSERVER) Closed
MSSQLServerOLAPService 4292 SQL Server Analysis Services (MSSQLSERVER) Running
SSASTELEMETRY SQL Server Analysis Services CEIP (MSSQLSERVER) Closed
SQLTELEMETRY 5012 SQL Server CEIP service (MSSQLSERVER) Running
SQL Server Distributed Replay Client SQL Server Distributed Replay Client Closed
SQL Server Distributed Replay Controller SQL Server Distributed Replay Controller Running
MsDtsServer130 4808 SQL Server Integration Services 13.0 Running
SSISTELEMETRY130 4764 SQL Server Integration Services CEIP service 13.0 Running
MSSQLLaunchpad SQL Server Launchpad (MSSQLSERVER) Closed
ReportServer 5076 SQL Server Reporting Services (MSSQLSERVER) Running
SQLWriter 2664 SQL Server VSS Writer Running
SQLSERVERAGENT SQL Server-Agent (MSSQLSERVER) Closed
SQLBrowser 3108 SQL Server-Browser Running
SQLPBDMS SQL Server-PolyBase-Datenverschiebung (MSSQLSERVER) Closed
SQLPBENGINE SQL Server-PolyBase-Modul (MSSQLSERVER) ClosedHere are the installed Programs and Features starting with Microsoft SQL.
- tokoc - Wednesday, August 30, 2017 2:09 AMWhen I connect to SSMS I use (localDb)\LocalSqlDW as Servername. That's a local created Server.
The results of your query are the following.MachineName: KOCH-T-NB
InstanceName:LOCALDB#4CB18A34
Edition:Express Edition (64-bit)
IsLocalDB: 1
Here are some more services including the closed ones.
Name PID Discription Status
MSSQLFDLauncher SQL Full-text Filter Daemon Launcher (MSSQLSERVER) Closed
MSSQLSERVER SQL Server (MSSQLSERVER) Closed
MSSQLServerOLAPService 4292 SQL Server Analysis Services (MSSQLSERVER) Running
SSASTELEMETRY SQL Server Analysis Services CEIP (MSSQLSERVER) Closed
SQLTELEMETRY 5012 SQL Server CEIP service (MSSQLSERVER) Running
SQL Server Distributed Replay Client SQL Server Distributed Replay Client Closed
SQL Server Distributed Replay Controller SQL Server Distributed Replay Controller Running
MsDtsServer130 4808 SQL Server Integration Services 13.0 Running
SSISTELEMETRY130 4764 SQL Server Integration Services CEIP service 13.0 Running
MSSQLLaunchpad SQL Server Launchpad (MSSQLSERVER) Closed
ReportServer 5076 SQL Server Reporting Services (MSSQLSERVER) Running
SQLWriter 2664 SQL Server VSS Writer Running
SQLSERVERAGENT SQL Server-Agent (MSSQLSERVER) Closed
SQLBrowser 3108 SQL Server-Browser Running
SQLPBDMS SQL Server-PolyBase-Datenverschiebung (MSSQLSERVER) Closed
SQLPBENGINE SQL Server-PolyBase-Modul (MSSQLSERVER) ClosedHere are the installed Programs and Features starting with Microsoft SQL.
As per above, your sql server is not running. Following Service should have "Running" Status:
MSSQLSERVER SQL Server (MSSQLSERVER) ClosedSSAS Service is running though.
MSSQLServerOLAPService 4292 SQL Server Analysis Services (MSSQLSERVER) Running
-
It looks like you have SQL Server Developer and LocalDB both.
The first issues with the connection attempts to LocalDB could be due to not sharing LocalDB. And that's just a guess as I don't know much about LocalDB but it would explain why you could only connect using your own account with the impersonation settings. No one else can connect until you share it. This Microsoft documentation has some more information on LocalDB, sharing and how that works:
SQL Server 2016 Express LocalDBStart the MSSQLSERVER service listed in the services. If you thought you installed the Developer edition, that is most likely the database engine service for that. I would think you would use this regular instance of SQL Server, the one listed as MSSQLSERVER in the services, for your work with Analysis Services project.
After you start that service, try to connect to it using SSMS. For the server, use your PC name or type in: (local)Hopefully that gets you to where you can connect to and work with both instances.
Sue
-
To share the LocalDB was a great advice!! I am able to process it, if I use the Administrator account!
Only one problem ist left. I can't connect with the Service Account. The following error occurs:OLE DB-Error: OLE DB- or ODBC-Error: Login failed for user 'NT-AUTORITÄT\SYSTEM'.; 28000.
It would be awsome, if you would also an idea how to fix this error. My Bachelors Thesis includes a case study and it wouldn't be cool to tell the reader to activate the Administrator account.
Tobi
- tokoc - Thursday, August 31, 2017 2:00 AM
Glad you got that part working.
For the other login failure, did you share with that service account too? NTAuthority\System is your local system account.
You can find more information for login failures in the error log for the instance. For LocalDB, I think the error log is located in the path of:
%LocalAppData%\Microsoft\Microsoft SQL Server Local DB\Intances\YourInstance....
Somewhere along the path should be the file error.log
Not totally sure on that as I don't use LocalDB. Otherwise, try looking for the error in the Application Event log for Windows.
Other versions of SQL Server will log a State number that can tell you why a login fails. This article has a very comprehensive list of the login failure reasons if you can find the state code:
Troubleshooting Error 18456Sue
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply