Home / server / Questions / 119409
In Process
ScaleOvenStove
Asked: 2010-03-05 19:46:53 +0800 CST

SQL Server Analysis Services, DNS, AD, Kerberos, Connection Issues

  • 772

Running into a very weird issue. Converting servers to Windows 2008/SQL 2008. Have a server, SERVER_A, brand new, setup with Win2k8,Sql2k8 - works. Have a Server SERVER_B, running Windows2003/SQL2005. I want to migrate from SERVER_B to SERVER_A. I have all db's, cubes, etc setup on SERVER_A and it is mimicking functionality.

Since users are using Excel to connect to SSAS, they connection string has SERVER_B in it. What I want to do, is, change DNS on the network to point SERVER_B (by name) at the ip of SERVER_A.

I have successfully done this with another server, SERVER_C, but I need to do it with SERVER_B.

What I have found is that with SERVER_C, after changing DNS, had to remove SERVER_C from AD and then it worked. I could connect to SERVER_C (DB), SERVER_C (SSAS Default Instance) and SERVER_C (SSAS Named instance) and it all was actually connecting to SERVER_A

I tried to do the same with with SERVER_B, and no luck. Changed DNS, removed from AD, and it wouldn't connect. Found out that there were some SPN's in AD set up, so removed those and tried again.

I then could connect to SERVER_B (DB), SERVER_B (SSAS Named Instance), but not SERVER_B (SSAS Default Instance). I could connect to SERVER_B (SSAS Default Intance WITH the Port #), but I need to be able to connect without the port number.

I am at a loss to as why I can't connect to the default instance without a port #. Not sure if it is SPN's in AD, or another AD issue, or something else. Pretty sure it isnt something on the server (because SERVER_C works!)

Any insight or suggestions would be greatly helpful!!

sql-server domain-name-system sql-server-2008 active-directory kerberos

1 Answers

  1. found out that we had to remove all SPN's from Active Directory before this would work.

    setspn -D

    also, found that there were some SPN's set to a domain account, so had to remove those

    setspn -D

    and then it all worked after changing DNS. It came down to the SPN's being the issue.

    first had to change sql services (DB, SSAS) on SERVER_B to local system, shut down gracefully, and then remove SPN's, change DNS and it worked, removed SERVER_B from AD and everything is good

    • 1