Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
0
Guests Online:
90

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

SPN config on SQL 2008 Mirrored Environment

Please wait...

SPN config on SQL 2008 Mirrored Environment

Postby aussiemeats1 on Fri Jul 06, 2012 6:22 pm

G'day Mates,

First post, please forgive me if this has been discussed before.

The problem:

I have 2 SQL 2008 Systems with a mirrored DB and a Witness Server.

I am trying to configure Kerberos Authentication so that some of our
people can login directly to the SQL Mangement Ap. and run Scripts

The current systems that are trying to logon are, Win 7-64 bit systems.

They are receiving this error when attempting to logon using Domain Credentials;

Cannot connect to RFSQL1.

===================================

Cannot generate SSPI context. (.Net SqlClient Data Provider)


When I run SETSPN -l on the primary server I get the following;

Registered ServicePrincipalNames for CN=RFSQL1,CN=Computers,DC=rancho,DC=local:
WSMAN/rfsql1
WSMAN/rfsql1.rancho.local
AcronisAgent/rfsql1.rancho.local
HOST/RFSQL1
HOST/rfsql1.rancho.local

There is no reference to MSSQLSvc or the Service Acct we're attempting to use to start the services ie SVCADMIN

I have modified the SVCADMIN Security Properties and Added SELF
and enabled the following items;

Read servicePrincipalName
Write servicePrincipalName

on both servers using Domain Administrator Credentials

I believe I need to add the following lines to each server;

MSSQLvc/RFSQL1.rancho.local:1433 SVCADMIN.rancho.local and or
MSSQLvc/RFSQL1.rancho.local: SVCADMIN.rancho.local

MSSQLvc/RFSQL2.rancho.local:1433 SVCADMIN.rancho.local and or
MSSQLvc/RFSQL2.rancho.local: SVCADMIN.rancho.local

The concern is;

because they're mirrored I'm not sure if there are ANY
other MODS I need to make or where I might need to make them
or even if the ones I have outlined are the correct mods or even
necessary.

My aim here is to enable Kerberos Authentication for Domain Users

Can anyone assist please?

The company that is our DB Manufacturer does not know how to resolve
this situation.

I'm concerned about potentially disrupting the mirror and or crashing the DB

Thanks and have a Good Day

Cheers
aussiemeats1
Newbie
Newbie
 
Posts: 2
Joined: Fri Jul 06, 2012 5:50 pm
Unrated

Re: SPN config on SQL 2008 Mirrored Environment

Postby onpnt on Fri Jul 06, 2012 6:38 pm

Your concerns about the potential disruption to the mirroring partnership is accurate. Can the users access other parts of the domain?

I would recommend running through Robert's list and troubleshooting here and see where you are at. But again, tread easy because you do have a potential of failing that mirror over if the authentication between the partnerships are lost.

http://www.sqlservercentral.com/blogs/r ... -Lesson-1/
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: SPN config on SQL 2008 Mirrored Environment

Postby SQLSoldier on Fri Jul 06, 2012 7:08 pm

98% of SSPI context errors are caused by an invalid SPN or by domain controller connectivity issues. If you have validated no bad SPNs exist, look in the system event log to see if there are any errors indicating domain controller issues such as kerberos errors or failures to connect to the domain controller within the last 24 hours.

DC connectivity issues can be resolved by rebooting the server.

As far as kerberos, all you need to do is create the SPNs, and you should be good.
SQLSoldier
Newbie
Newbie
 
Posts: 2
Joined: Thu Apr 12, 2012 2:33 am
Unrated

Re: SPN config on SQL 2008 Mirrored Environment

Postby aussiemeats1 on Sat Jul 07, 2012 3:07 pm

G'Day,

Thank you for the responses,

1. ONPNT ...Thanks for the advice I am going thru that as we speak.

When I run the Setspn -l RFSQL1 command on the RFSQL1 Server server, I get;

Registered ServicePrincipalNames for CN=RFSQL1,CN=Computers,DC=rancho,DC=local:
WSMAN/rfsql1
WSMAN/rfsql1.rancho.local
AcronisAgent/rfsql1.rancho.local
HOST/RFSQL1
HOST/rfsql1.rancho.local

When I run the setspn -l rancho\SVCADMIN command on the SVCADMIN Service account I get;

MSSQLSvc/RFSQL2.rancho.local:1433
MSSQLSvc/RFSQL2.rancho.local
MSSQLSvc/RFSQL1.rancho.local
MSSQLSvc/RFSQL1.rancho.local:1433

So it looks like the correct SPN info is under the SVCADMIN account

My question is do i need to also add those lines to each Server as well? RFSQL1 & RFSQL2?

Does that HOOK need to be in each servers setspn -l RFSQL1 results?


2. SQL Soldier...Thank you but my question is; Shall I add the following lines to each respective SQL server;

MSSQLvc/RFSQL1.rancho.local:1433 SVCADMIN.rancho.local and or
MSSQLvc/RFSQL1.rancho.local: SVCADMIN.rancho.local

MSSQLvc/RFSQL2.rancho.local:1433 SVCADMIN.rancho.local and or
MSSQLvc/RFSQL2.rancho.local: SVCADMIN.rancho.local

and if so is there a possibility that it may impact the Mirror?

Secondly, Much of the Literature I'm reading regarding Mirrored
architechure's, indicates that you can/should configure the SPN's
to reflect the roles of the two servers....

Primary SQL Server and
Failover SQL server

is this applicable concerning my configuration and if so,
What is the correct CLI commands to configure it that way?

Thank you again all

Regards
aussiemeats1
Newbie
Newbie
 
Posts: 2
Joined: Fri Jul 06, 2012 5:50 pm
Unrated