Introduction

It’s not immediately obvious how to pass the proper credentials in an environment where there is more than one Active Directory domain and you need to connect to resources using Windows Integrated Authentication from another domain. The SQL Server database engine gives us the option of SQL Server authentication; but, what about services like SQL Server Analysis Services?  Fortunately, we can get our credentials across by using the runas command with a flag.  Want to know how?  Read on!

Environment

Let’s say you’re working somewhere with more than one domain.  Perhaps your company acquired another and the domains haven’t been merged, or they don’t trust each other.  Perhaps you’re a consultant, but you don’t log into your machine with an Active Directory account from the network you’re trying to access.  Services like SQL Server Analysis Services (SSAS) don’t provide authentication options beyond Windows Integrated Authentication.  If you try to connect to an Analysis Services cube, say in Excel, with the credentials you logged in with you’d get an error like:

Initialization of the data source has failed.  Check the database server or contact your database administrator.  Make sure the external database is available, and then try the operation again.  If you see this message again, create a new data source to connect to the database.

It’s a pretty generic error.  You’re a database professional, so if the system you’re trying to access weren’t available, you’d already know about it.  If you were to try it in SQL Server Management Studio (SSMS), you’d see an error like:

Cannot connect to SERVERNAME. 

Additional information:

A connection cannot be made.  Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

This error is a bit more descriptive and it hints at an authentication problem.  At this point, you have likely realized that the account you used to log into your workstation doesn’t have permissions to log into SSAS.  So you request an account for yourself in that domain and have the administrators make sure that this account has permissions to connect to the SSAS database in question.  Now we just need to use those credentials from your workstation without logging in with them.

Runas /netonly:

The runas command can be used to run applications under another account, but it typically tries to confirm the credentials in your own domain which will fail if you use an account in another domain.  The /netonly option tells runas that the account is in a remote network so it won’t do this check.  But if you open up a CMD prompt, you can enter a command that’s something like this to open SSMS using the right credentials.  To demonstrate, let’s say the other domain is called DOMAIN2 and the AD account is demoaccount:

runas.exe /user:DOMAIN2\demoaccount /netonly “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe”

When you run it, you’ll be prompted for the password for this account.  Type it in, although you won’t see anything in the command window as you type, and hit ENTER.  Then SSMS will open up.  If the password was entered incorrectly, there won’t be any way to tell that it was wrong.  Your credentials just won’t work.  If the user name and password were entered correctly, you’ll be able to connect to the SSAS server.  Depending on network conditions, you may need to use the IP address instead of the server name if there’s a DNS issue or look into getting ports unblocked if you can’t connect to it.  If you were using Excel, at this point you would be able connect to the SSAS cube:

Previously we got an error here.

Not anymore.

To save time, I usually create a shortcut on my desktop to allow me to launch the application with the correct permissions quickly:

You’ll have to make sure that if you have other instances of the application (SSMS, Excel, Business Intelligence Development Studio, etc) open, that you choose the one opened with the credentials in the other domain when you need to connect.

Conclusion

Although it’s not apparent, connecting to resources on untrusted domains is possible by using runas with the /netonly flag.  A variety of applications that can be used for working with data, like Excel, SSMS and Business Intelligence Development Studio, can be opened this way to allow the permissions of other accounts to be presented to SQL Server Analysis Services or even just SQL Server.