2

SQL Server Analysis Services uses Windows authentication for server connections. Typically, you assign your domain account to a role (or the server administrator list), and then connect to the server from a user session using the same domain account. In non-domain environments, you can accomplish the same functionality by using the same username and password on both accounts.

I am trying to see if it is possible to store a credential for the Analysis Server login in the Windows credential manager, so this login can be arbitrarily defined instead of tied to the current user session.

For regular SQL Server connections with Windows Authentication from SSMS, this is possible through a workaround: include the FQDN and SQL Server port number in your stored credential. In other words, you open Credential Manager from the Windows control panel, create a new credential for server: sqlserver.example.com:1433, and use the username and password for an account on that server that is granted a SQL logon. Is something similar necessary for an Analysis Services stored credential?

I have already tried defining credentials as one would for SQL Server, using the SQL Server Browser service port and a non-dynamic Analysis Services port, but was unsuccessful. There is no option in SSMS to input a logon and then save it to the credential manager for Windows Auth, as there would be for a file share or remote desktop connection.

NReilingh
  • 484
  • 3
  • 9
  • 24

2 Answers2

0

I was able to get this working with a similar work around: Put the server name and credentials into Credential Manager. It worked when I put in the computer name (reported from Control Panel\System and Security\System) without the port number. In our case, it was DEV-DB even though I can't resolve that name from my machine.

I used the IP address to connect from SSMS. I had both default and named instances. They used the same credentials in the credential manager.

John Tseng
  • 155
  • 4
0

You need to create two seperate credentials, one for Analysis Services (without a port) and one for Database Engine (with a port).

Open a command line on the server and run command 

ipconfig -all

Make a note of the host name and the primary dns suffix.

On the local machine go to Control Panel/User Accounts/Credential Manager Click on Windows credentials.

Add a windows credential for analysis services.

For the internet or network address, enter the host name and the primary dns suffix with a '.' in between. e.g. 

myserver.mydomain.com

Enter the qualified (with domain) username and password. e.g. 

mydomain\username

mypassword

Enter another credential to allow the use of Database Engine. Do the same as for Analysis services but after the server name, enter the port

e.g. myserver.mydomain.com:1433

Then, when you connect from SQL Server Management Studio, use the FQN for the server.

e.g. myserver.mydomain.com

Andrew Grant
  • 1