We are running a Windows 2003 domain. I have a Windows 2003 member server
running an app. and MSDE.
I now have another app. that I would like to install on this same server.
The new app. also needs MSDE. My question is can I install and run more
then one copy of MSDE? Is there anything I need to know about doing it?
TIA,
ClaytonOr can I just use the same MSDE for the new app.? Can MSDE have more the
one DB?
TIA,
Clayton
"Clayton" <none@.none.com> wrote in message
news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
> We are running a Windows 2003 domain. I have a Windows 2003 member server
> running an app. and MSDE.
> I now have another app. that I would like to install on this same server.
> The new app. also needs MSDE. My question is can I install and run more
> then one copy of MSDE? Is there anything I need to know about doing it?
>
> TIA,
> Clayton
>|||Can both apps. share the same MSDE install?
TIA,
Clayton
"Clayton" <none@.none.com> wrote in message
news:DNhog.34477$Bh2.16824@.fe07.news.easynews.com...
> Or can I just use the same MSDE for the new app.? Can MSDE have more the
> one DB?
>
> TIA,
> Clayton
>
> "Clayton" <none@.none.com> wrote in message
> news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
>> We are running a Windows 2003 domain. I have a Windows 2003 member
>> server running an app. and MSDE.
>> I now have another app. that I would like to install on this same server.
>> The new app. also needs MSDE. My question is can I install and run more
>> then one copy of MSDE? Is there anything I need to know about doing it?
>>
>> TIA,
>> Clayton
>|||Yes, you can. It just requires a different instance name than the other
installed instances.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Clayton" <none@.none.com> wrote in message
news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
We are running a Windows 2003 domain. I have a Windows 2003 member server
running an app. and MSDE.
I now have another app. that I would like to install on this same server.
The new app. also needs MSDE. My question is can I install and run more
then one copy of MSDE? Is there anything I need to know about doing it?
TIA,
Clayton|||You can install up to 16 instances of MSDE (I think that is still
correct -someone correct me if I'm wrong.) on the same server.
However, each additional instance will have a variation in the instance
name. For example, if your server is MyServer, the first installation of
MSDE (or SQL Server) will be know as MyServer. When you install a second
instance of MSDE, you are required to provide an 'instance' name (e.g.,
Accounting). Then that server/instance will be referred to as
MyServer\Accounting. You have to use the compounded name in all connection
strings, etc.
That may not be a simple issue for many third party applications. (I would
hope so -but I've learned to be pessimistic about the skills of a lot of
programmers.)
[Loud retort from over my shoulder ="Hey, I represent that remark!"]
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Clayton" <none@.none.com> wrote in message
news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
> We are running a Windows 2003 domain. I have a Windows 2003 member server
> running an app. and MSDE.
> I now have another app. that I would like to install on this same server.
> The new app. also needs MSDE. My question is can I install and run more
> then one copy of MSDE? Is there anything I need to know about doing it?
>
> TIA,
> Clayton
>|||Cool, thanks guys. I have to go live with this thing (BlackBerry) within
the hour!
Thanks for your help,
Clayton
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O7BA$QjmGHA.3732@.TK2MSFTNGP05.phx.gbl...
> You can install up to 16 instances of MSDE (I think that is still
> correct -someone correct me if I'm wrong.) on the same server.
> However, each additional instance will have a variation in the instance
> name. For example, if your server is MyServer, the first installation of
> MSDE (or SQL Server) will be know as MyServer. When you install a second
> instance of MSDE, you are required to provide an 'instance' name (e.g.,
> Accounting). Then that server/instance will be referred to as
> MyServer\Accounting. You have to use the compounded name in all connection
> strings, etc.
> That may not be a simple issue for many third party applications. (I would
> hope so -but I've learned to be pessimistic about the skills of a lot of
> programmers.)
> [Loud retort from over my shoulder ="Hey, I represent that remark!"]
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Clayton" <none@.none.com> wrote in message
> news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
>> We are running a Windows 2003 domain. I have a Windows 2003 member
>> server running an app. and MSDE.
>> I now have another app. that I would like to install on this same server.
>> The new app. also needs MSDE. My question is can I install and run more
>> then one copy of MSDE? Is there anything I need to know about doing it?
>>
>> TIA,
>> Clayton
>|||That depends on the app. The app may expect a particular instance name, for
example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Clayton" <none@.none.com> wrote in message
news:DNhog.34477$Bh2.16824@.fe07.news.easynews.com...
Or can I just use the same MSDE for the new app.? Can MSDE have more the
one DB?
TIA,
Clayton
"Clayton" <none@.none.com> wrote in message
news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
> We are running a Windows 2003 domain. I have a Windows 2003 member server
> running an app. and MSDE.
> I now have another app. that I would like to install on this same server.
> The new app. also needs MSDE. My question is can I install and run more
> then one copy of MSDE? Is there anything I need to know about doing it?
>
> TIA,
> Clayton
>|||Yes, one instance of MSDE (which is a version of SQL Server) can handle
quite a few databases. Depends on hardware, traffic, etc., subject to the
MSDE sizing limitations.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Clayton" <none@.none.com> wrote in message
news:OOhog.362227$Az2.335116@.fe10.news.easynews.com...
> Can both apps. share the same MSDE install?
> TIA,
> Clayton
>
> "Clayton" <none@.none.com> wrote in message
> news:DNhog.34477$Bh2.16824@.fe07.news.easynews.com...
>> Or can I just use the same MSDE for the new app.? Can MSDE have more the
>> one DB?
>>
>> TIA,
>> Clayton
>>
>> "Clayton" <none@.none.com> wrote in message
>> news:zEhog.34345$Bh2.22632@.fe07.news.easynews.com...
>> We are running a Windows 2003 domain. I have a Windows 2003 member
>> server running an app. and MSDE.
>> I now have another app. that I would like to install on this same
>> server. The new app. also needs MSDE. My question is can I install and
>> run more then one copy of MSDE? Is there anything I need to know about
>> doing it?
>>
>> TIA,
>> Clayton
>>
>
Showing posts with label domain. Show all posts
Showing posts with label domain. Show all posts
Thursday, March 29, 2012
Can I impersonate a userid in sql server odbc connection to Sql Server 2000?
My client gave me a domain/userid which I use with terminal server and mapped network drives to do work for them. However, I'd like to set up odbc access from my client pc (which is not on their domain) to their database for testing. It seems like you only have 2 choices - trusted authentication or sql authentication. Is there any way to use trusted authentication but change the userid? This would be similar to the map network drive "connect using a different user id" function.
I'm connecting to a Win2003 server running SQL Server 2000 from a WinXP PC.
Not realy, if you are using trusted connections you need to have a valid username and password that is allowed on the sql server. Is there a way that you could get the DBA to add your workstation\username to the database, then you should be able to pass your details... The other option would be to get another user set up that you could terminal serve in with and test with that.
|||Thanks for the answer. I do use terminal server but I have a problem in that once i minimize the screen or close it and come back in, my access database that is running minimizes and won't come back. So I thought I'd use odbc from my workstation to check on the status of the programs. I'll pursue having them add my workstation and userid as a trusted userid on that server.Thursday, March 8, 2012
Can connect with IP number but not with machine name
Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?
More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?
More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?
Can connect with IP number but not with machine name
Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alia
s
> for the IP number and that works but an alias for the machine name does no
t.
> With a DHCP server, the IP address isn't permanent so how do I configure t
he
> connection in my application?
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alia
s
> for the IP number and that works but an alias for the machine name does no
t.
> With a DHCP server, the IP address isn't permanent so how do I configure t
he
> connection in my application?
Can connect with IP number but not with machine name
Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?
Saturday, February 25, 2012
Can anyone get me out of SSPI / Kerberos / NTLM hell ??
This is to do with SQL Server 2005, but I think it is a more general issue.
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcome
Are the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pro.../tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SPNs
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pro.../tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
[vbcol=seagreen]
> Thanks Remus, you were spot on with that.
> We hadn't noticed that when we bounced the SQL instance that it was logging
> that it couldn't unregister the SPN.
> Not 100% sure how we got into that state, but not important.
> Thanks again,
> Al
> "Remus Rusanu [MSFT]" wrote:
|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
[vbcol=seagreen]
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
[vbcol=seagreen]
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
|||I was able to implement this by doing the following within AD (my
service account is not a domain admin):
One time only, for the service account:
1. Service account trusted for delegation (check)
2. Service account is sensitive and cannot be delegated (un-check)
3. Service account - two privileges required, read and write service
principal name, to allow SQL Service startup account to register spn
automatically
Once per each server:
4. Each SQL Server: computer account trusted for delegation
As your account is a domain admin, perhaps step 4 is all that you're
missing.
See MSKB 319723 for more info.
http://support.microsoft.com/kb/319723
Glenn
On Jan 11, 11:46 am, BeezelDub <jam...@.noemail.noemail> wrote:[vbcol=seagreen]
> When Kerberos is failing, a setspn -L for the SQL service domain account
> lists...
> MSSQLSvc/cluster1.domain.com:1433
> MSSQLSvc/cluster1.domain.com
> MSSQLSvc/cluster2.domain.com
> It's missing the SPN for the host node of the databases that are all of a
> sudden unaccessible via Kerberos.
> Manually setting the SPN...
> setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
> Fixes the issue immediately.
> To my knowledge, cluster1 has not had this SPN problem but then again, there
> are no apps requiring Kerberos hitting databases on that node. In handling
> this issue in the past, only cluster2 loses the SPN for whatever reason.
> It's nice knowing the fix, but its not THE FIX. Certainly there is some
> magic checkbox somewhere that will allow this SPN to be set automatically or
> not be lost in the first place.
> Thanks for working with me on this!
> "Al" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcome
Are the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pro.../tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SPNs
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pro.../tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
[vbcol=seagreen]
> Thanks Remus, you were spot on with that.
> We hadn't noticed that when we bounced the SQL instance that it was logging
> that it couldn't unregister the SPN.
> Not 100% sure how we got into that state, but not important.
> Thanks again,
> Al
> "Remus Rusanu [MSFT]" wrote:
|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
[vbcol=seagreen]
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
[vbcol=seagreen]
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
|||I was able to implement this by doing the following within AD (my
service account is not a domain admin):
One time only, for the service account:
1. Service account trusted for delegation (check)
2. Service account is sensitive and cannot be delegated (un-check)
3. Service account - two privileges required, read and write service
principal name, to allow SQL Service startup account to register spn
automatically
Once per each server:
4. Each SQL Server: computer account trusted for delegation
As your account is a domain admin, perhaps step 4 is all that you're
missing.
See MSKB 319723 for more info.
http://support.microsoft.com/kb/319723
Glenn
On Jan 11, 11:46 am, BeezelDub <jam...@.noemail.noemail> wrote:[vbcol=seagreen]
> When Kerberos is failing, a setspn -L for the SQL service domain account
> lists...
> MSSQLSvc/cluster1.domain.com:1433
> MSSQLSvc/cluster1.domain.com
> MSSQLSvc/cluster2.domain.com
> It's missing the SPN for the host node of the databases that are all of a
> sudden unaccessible via Kerberos.
> Manually setting the SPN...
> setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
> Fixes the issue immediately.
> To my knowledge, cluster1 has not had this SPN problem but then again, there
> are no apps requiring Kerberos hitting databases on that node. In handling
> this issue in the past, only cluster2 loses the SPN for whatever reason.
> It's nice knowing the fix, but its not THE FIX. Certainly there is some
> magic checkbox somewhere that will allow this SPN to be set automatically or
> not be lost in the first place.
> Thanks for working with me on this!
> "Al" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
Can anyone get me out of SSPI / Kerberos / NTLM hell ??
This is to do with SQL Server 2005, but I think it is a more general issue.
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcome
Are the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pro.../tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SPNs
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pro.../tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
[vbcol=seagreen]
> Thanks Remus, you were spot on with that.
> We hadn't noticed that when we bounced the SQL instance that it was logging
> that it couldn't unregister the SPN.
> Not 100% sure how we got into that state, but not important.
> Thanks again,
> Al
> "Remus Rusanu [MSFT]" wrote:
|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
[vbcol=seagreen]
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
[vbcol=seagreen]
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
|||I was able to implement this by doing the following within AD (my
service account is not a domain admin):
One time only, for the service account:
1. Service account trusted for delegation (check)
2. Service account is sensitive and cannot be delegated (un-check)
3. Service account - two privileges required, read and write service
principal name, to allow SQL Service startup account to register spn
automatically
Once per each server:
4. Each SQL Server: computer account trusted for delegation
As your account is a domain admin, perhaps step 4 is all that you're
missing.
See MSKB 319723 for more info.
http://support.microsoft.com/kb/319723
Glenn
On Jan 11, 11:46 am, BeezelDub <jam...@.noemail.noemail> wrote:[vbcol=seagreen]
> When Kerberos is failing, a setspn -L for the SQL service domain account
> lists...
> MSSQLSvc/cluster1.domain.com:1433
> MSSQLSvc/cluster1.domain.com
> MSSQLSvc/cluster2.domain.com
> It's missing the SPN for the host node of the databases that are all of a
> sudden unaccessible via Kerberos.
> Manually setting the SPN...
> setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
> Fixes the issue immediately.
> To my knowledge, cluster1 has not had this SPN problem but then again, there
> are no apps requiring Kerberos hitting databases on that node. In handling
> this issue in the past, only cluster2 loses the SPN for whatever reason.
> It's nice knowing the fix, but its not THE FIX. Certainly there is some
> magic checkbox somewhere that will allow this SPN to be set automatically or
> not be lost in the first place.
> Thanks for working with me on this!
> "Al" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcome
Are the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pro.../tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome
|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SPNs
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pro.../tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
[vbcol=seagreen]
> Thanks Remus, you were spot on with that.
> We hadn't noticed that when we bounced the SQL instance that it was logging
> that it couldn't unregister the SPN.
> Not 100% sure how we got into that state, but not important.
> Thanks again,
> Al
> "Remus Rusanu [MSFT]" wrote:
|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
[vbcol=seagreen]
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
[vbcol=seagreen]
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
|||I was able to implement this by doing the following within AD (my
service account is not a domain admin):
One time only, for the service account:
1. Service account trusted for delegation (check)
2. Service account is sensitive and cannot be delegated (un-check)
3. Service account - two privileges required, read and write service
principal name, to allow SQL Service startup account to register spn
automatically
Once per each server:
4. Each SQL Server: computer account trusted for delegation
As your account is a domain admin, perhaps step 4 is all that you're
missing.
See MSKB 319723 for more info.
http://support.microsoft.com/kb/319723
Glenn
On Jan 11, 11:46 am, BeezelDub <jam...@.noemail.noemail> wrote:[vbcol=seagreen]
> When Kerberos is failing, a setspn -L for the SQL service domain account
> lists...
> MSSQLSvc/cluster1.domain.com:1433
> MSSQLSvc/cluster1.domain.com
> MSSQLSvc/cluster2.domain.com
> It's missing the SPN for the host node of the databases that are all of a
> sudden unaccessible via Kerberos.
> Manually setting the SPN...
> setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
> Fixes the issue immediately.
> To my knowledge, cluster1 has not had this SPN problem but then again, there
> are no apps requiring Kerberos hitting databases on that node. In handling
> this issue in the past, only cluster2 loses the SPN for whatever reason.
> It's nice knowing the fix, but its not THE FIX. Certainly there is some
> magic checkbox somewhere that will allow this SPN to be set automatically or
> not be lost in the first place.
> Thanks for working with me on this!
> "Al" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
Can anyone get me out of SSPI / Kerberos / NTLM hell ??
This is to do with SQL Server 2005, but I think it is a more general issue.
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcomeAre the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > This is to do with SQL Server 2005, but I think it is a more general
> > issue.
> >
> > We have two SQL Servers, but starting with bog-standard Domain accounts
> > and
> > both running in Windows Authentication mode. Either account it trust for
> > delegation, nor are the machines, as we do not need delegation and don't
> > which to turn it on.
> >
> > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > connection and we can see that is it using NTLM for authentication.
> >
> > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > "Cannot
> > generate SSPI context". When trying to attach using Named Pipes, get a
> > connection and again we can see that it is using NTLM for authentication.
> >
> > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > tell) IP Security configured.
> >
> > We are using Active Directory 2003, running in Native Mode.
> >
> > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > Server
> > #2.
> >
> > All ideas most welcome
>
>|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SPNs
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > This is to do with SQL Server 2005, but I think it is a more general
> > issue.
> >
> > We have two SQL Servers, but starting with bog-standard Domain accounts
> > and
> > both running in Windows Authentication mode. Either account it trust for
> > delegation, nor are the machines, as we do not need delegation and don't
> > which to turn it on.
> >
> > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > connection and we can see that is it using NTLM for authentication.
> >
> > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > "Cannot
> > generate SSPI context". When trying to attach using Named Pipes, get a
> > connection and again we can see that it is using NTLM for authentication.
> >
> > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > tell) IP Security configured.
> >
> > We are using Active Directory 2003, running in Native Mode.
> >
> > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > Server
> > #2.
> >
> > All ideas most welcome
>
>|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
> Thanks Remus, you were spot on with that.
> We hadn't noticed that when we bounced the SQL instance that it was logging
> that it couldn't unregister the SPN.
> Not 100% sure how we got into that state, but not important.
> Thanks again,
> Al
> "Remus Rusanu [MSFT]" wrote:
> > I suspect Server #2 has an SPN registered for the SQL Server, but the
> > account running the server has no priviledges over that SPN. Check what SPNs
> > are registered for the two machines. Use a tool like setspn.exe (available
> > for download from microsoft download center).
> >
> > This article is very usefull on troubleshooting these kind of issues:
> > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > HTH,
> > ~ Remus Rusanu
> >
> > SQL Service Broker
> > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > > This is to do with SQL Server 2005, but I think it is a more general
> > > issue.
> > >
> > > We have two SQL Servers, but starting with bog-standard Domain accounts
> > > and
> > > both running in Windows Authentication mode. Either account it trust for
> > > delegation, nor are the machines, as we do not need delegation and don't
> > > which to turn it on.
> > >
> > > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > > connection and we can see that is it using NTLM for authentication.
> > >
> > > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > > "Cannot
> > > generate SSPI context". When trying to attach using Named Pipes, get a
> > > connection and again we can see that it is using NTLM for authentication.
> > >
> > > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > > tell) IP Security configured.
> > >
> > > We are using Active Directory 2003, running in Native Mode.
> > >
> > > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > > Server
> > > #2.
> > >
> > > All ideas most welcome
> >
> >
> >|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
> > Thanks Remus, you were spot on with that.
> > We hadn't noticed that when we bounced the SQL instance that it was logging
> > that it couldn't unregister the SPN.
> >
> > Not 100% sure how we got into that state, but not important.
> >
> > Thanks again,
> >
> > Al
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> > > I suspect Server #2 has an SPN registered for the SQL Server, but the
> > > account running the server has no priviledges over that SPN. Check what SPNs
> > > are registered for the two machines. Use a tool like setspn.exe (available
> > > for download from microsoft download center).
> > >
> > > This article is very usefull on troubleshooting these kind of issues:
> > > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > HTH,
> > > ~ Remus Rusanu
> > >
> > > SQL Service Broker
> > > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> > >
> > >
> > > "Al" <Al@.discussions.microsoft.com> wrote in message
> > > news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > > > This is to do with SQL Server 2005, but I think it is a more general
> > > > issue.
> > > >
> > > > We have two SQL Servers, but starting with bog-standard Domain accounts
> > > > and
> > > > both running in Windows Authentication mode. Either account it trust for
> > > > delegation, nor are the machines, as we do not need delegation and don't
> > > > which to turn it on.
> > > >
> > > > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > > > connection and we can see that is it using NTLM for authentication.
> > > >
> > > > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > > > "Cannot
> > > > generate SSPI context". When trying to attach using Named Pipes, get a
> > > > connection and again we can see that it is using NTLM for authentication.
> > > >
> > > > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > > > tell) IP Security configured.
> > > >
> > > > We are using Active Directory 2003, running in Native Mode.
> > > >
> > > > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > > > Server
> > > > #2.
> > > >
> > > > All ideas most welcome
> > >
> > >
> > >|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
> > This one has me in hell too and SPN is my issue as well, but I'm trying to
> > find a less manual solution because this one keeps popping up.
> >
> > We're running SQL Server 2005 cluster (active/active). SQL server services
> > on both nodes is running under a domain account that has been added to the
> > Domain Admins group. Sporadically, whenever we fail over or reboot the
> > cluster, the SPN for one of the cluster nodes is lost killing all my apps
> > that rely on Kerberos authentication. Manually adding an SPN for the SQL
> > server service account resolves the issue, but this always incurs down time.
> >
> > Why is the SPN not being created automatically? Permission issue? I've read
> > that this is only a problem when running the SQL service under a domain
> > account. Shouldn't being part of the Domain Admin group supply sufficient
> > permissions to enable the domain account to create the SPN on service startup?
> >
> > Any help with this is GREATLY appreciated.
> >
> > James
> >
> >
> >
> >
> > "Al" wrote:
> >
> > > Thanks Remus, you were spot on with that.
> > > We hadn't noticed that when we bounced the SQL instance that it was logging
> > > that it couldn't unregister the SPN.
> > >
> > > Not 100% sure how we got into that state, but not important.
> > >
> > > Thanks again,
> > >
> > > Al
> > >
> > > "Remus Rusanu [MSFT]" wrote:
> > >
> > > > I suspect Server #2 has an SPN registered for the SQL Server, but the
> > > > account running the server has no priviledges over that SPN. Check what SPNs
> > > > are registered for the two machines. Use a tool like setspn.exe (available
> > > > for download from microsoft download center).
> > > >
> > > > This article is very usefull on troubleshooting these kind of issues:
> > > > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > >
> > > > HTH,
> > > > ~ Remus Rusanu
> > > >
> > > > SQL Service Broker
> > > > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> > > >
> > > >
> > > > "Al" <Al@.discussions.microsoft.com> wrote in message
> > > > news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > > > > This is to do with SQL Server 2005, but I think it is a more general
> > > > > issue.
> > > > >
> > > > > We have two SQL Servers, but starting with bog-standard Domain accounts
> > > > > and
> > > > > both running in Windows Authentication mode. Either account it trust for
> > > > > delegation, nor are the machines, as we do not need delegation and don't
> > > > > which to turn it on.
> > > > >
> > > > > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > > > > connection and we can see that is it using NTLM for authentication.
> > > > >
> > > > > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > > > > "Cannot
> > > > > generate SSPI context". When trying to attach using Named Pipes, get a
> > > > > connection and again we can see that it is using NTLM for authentication.
> > > > >
> > > > > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > > > > tell) IP Security configured.
> > > > >
> > > > > We are using Active Directory 2003, running in Native Mode.
> > > > >
> > > > > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > > > > Server
> > > > > #2.
> > > > >
> > > > > All ideas most welcome
> > > >
> > > >
> > > >
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcomeAre the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > This is to do with SQL Server 2005, but I think it is a more general
> > issue.
> >
> > We have two SQL Servers, but starting with bog-standard Domain accounts
> > and
> > both running in Windows Authentication mode. Either account it trust for
> > delegation, nor are the machines, as we do not need delegation and don't
> > which to turn it on.
> >
> > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > connection and we can see that is it using NTLM for authentication.
> >
> > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > "Cannot
> > generate SSPI context". When trying to attach using Named Pipes, get a
> > connection and again we can see that it is using NTLM for authentication.
> >
> > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > tell) IP Security configured.
> >
> > We are using Active Directory 2003, running in Native Mode.
> >
> > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > Server
> > #2.
> >
> > All ideas most welcome
>
>|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SPNs
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > This is to do with SQL Server 2005, but I think it is a more general
> > issue.
> >
> > We have two SQL Servers, but starting with bog-standard Domain accounts
> > and
> > both running in Windows Authentication mode. Either account it trust for
> > delegation, nor are the machines, as we do not need delegation and don't
> > which to turn it on.
> >
> > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > connection and we can see that is it using NTLM for authentication.
> >
> > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > "Cannot
> > generate SSPI context". When trying to attach using Named Pipes, get a
> > connection and again we can see that it is using NTLM for authentication.
> >
> > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > tell) IP Security configured.
> >
> > We are using Active Directory 2003, running in Native Mode.
> >
> > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > Server
> > #2.
> >
> > All ideas most welcome
>
>|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
> Thanks Remus, you were spot on with that.
> We hadn't noticed that when we bounced the SQL instance that it was logging
> that it couldn't unregister the SPN.
> Not 100% sure how we got into that state, but not important.
> Thanks again,
> Al
> "Remus Rusanu [MSFT]" wrote:
> > I suspect Server #2 has an SPN registered for the SQL Server, but the
> > account running the server has no priviledges over that SPN. Check what SPNs
> > are registered for the two machines. Use a tool like setspn.exe (available
> > for download from microsoft download center).
> >
> > This article is very usefull on troubleshooting these kind of issues:
> > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > HTH,
> > ~ Remus Rusanu
> >
> > SQL Service Broker
> > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > > This is to do with SQL Server 2005, but I think it is a more general
> > > issue.
> > >
> > > We have two SQL Servers, but starting with bog-standard Domain accounts
> > > and
> > > both running in Windows Authentication mode. Either account it trust for
> > > delegation, nor are the machines, as we do not need delegation and don't
> > > which to turn it on.
> > >
> > > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > > connection and we can see that is it using NTLM for authentication.
> > >
> > > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > > "Cannot
> > > generate SSPI context". When trying to attach using Named Pipes, get a
> > > connection and again we can see that it is using NTLM for authentication.
> > >
> > > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > > tell) IP Security configured.
> > >
> > > We are using Active Directory 2003, running in Native Mode.
> > >
> > > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > > Server
> > > #2.
> > >
> > > All ideas most welcome
> >
> >
> >|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
> > Thanks Remus, you were spot on with that.
> > We hadn't noticed that when we bounced the SQL instance that it was logging
> > that it couldn't unregister the SPN.
> >
> > Not 100% sure how we got into that state, but not important.
> >
> > Thanks again,
> >
> > Al
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> > > I suspect Server #2 has an SPN registered for the SQL Server, but the
> > > account running the server has no priviledges over that SPN. Check what SPNs
> > > are registered for the two machines. Use a tool like setspn.exe (available
> > > for download from microsoft download center).
> > >
> > > This article is very usefull on troubleshooting these kind of issues:
> > > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > HTH,
> > > ~ Remus Rusanu
> > >
> > > SQL Service Broker
> > > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> > >
> > >
> > > "Al" <Al@.discussions.microsoft.com> wrote in message
> > > news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > > > This is to do with SQL Server 2005, but I think it is a more general
> > > > issue.
> > > >
> > > > We have two SQL Servers, but starting with bog-standard Domain accounts
> > > > and
> > > > both running in Windows Authentication mode. Either account it trust for
> > > > delegation, nor are the machines, as we do not need delegation and don't
> > > > which to turn it on.
> > > >
> > > > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > > > connection and we can see that is it using NTLM for authentication.
> > > >
> > > > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > > > "Cannot
> > > > generate SSPI context". When trying to attach using Named Pipes, get a
> > > > connection and again we can see that it is using NTLM for authentication.
> > > >
> > > > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > > > tell) IP Security configured.
> > > >
> > > > We are using Active Directory 2003, running in Native Mode.
> > > >
> > > > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > > > Server
> > > > #2.
> > > >
> > > > All ideas most welcome
> > >
> > >
> > >|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
> > This one has me in hell too and SPN is my issue as well, but I'm trying to
> > find a less manual solution because this one keeps popping up.
> >
> > We're running SQL Server 2005 cluster (active/active). SQL server services
> > on both nodes is running under a domain account that has been added to the
> > Domain Admins group. Sporadically, whenever we fail over or reboot the
> > cluster, the SPN for one of the cluster nodes is lost killing all my apps
> > that rely on Kerberos authentication. Manually adding an SPN for the SQL
> > server service account resolves the issue, but this always incurs down time.
> >
> > Why is the SPN not being created automatically? Permission issue? I've read
> > that this is only a problem when running the SQL service under a domain
> > account. Shouldn't being part of the Domain Admin group supply sufficient
> > permissions to enable the domain account to create the SPN on service startup?
> >
> > Any help with this is GREATLY appreciated.
> >
> > James
> >
> >
> >
> >
> > "Al" wrote:
> >
> > > Thanks Remus, you were spot on with that.
> > > We hadn't noticed that when we bounced the SQL instance that it was logging
> > > that it couldn't unregister the SPN.
> > >
> > > Not 100% sure how we got into that state, but not important.
> > >
> > > Thanks again,
> > >
> > > Al
> > >
> > > "Remus Rusanu [MSFT]" wrote:
> > >
> > > > I suspect Server #2 has an SPN registered for the SQL Server, but the
> > > > account running the server has no priviledges over that SPN. Check what SPNs
> > > > are registered for the two machines. Use a tool like setspn.exe (available
> > > > for download from microsoft download center).
> > > >
> > > > This article is very usefull on troubleshooting these kind of issues:
> > > > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > >
> > > > HTH,
> > > > ~ Remus Rusanu
> > > >
> > > > SQL Service Broker
> > > > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> > > >
> > > >
> > > > "Al" <Al@.discussions.microsoft.com> wrote in message
> > > > news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> > > > > This is to do with SQL Server 2005, but I think it is a more general
> > > > > issue.
> > > > >
> > > > > We have two SQL Servers, but starting with bog-standard Domain accounts
> > > > > and
> > > > > both running in Windows Authentication mode. Either account it trust for
> > > > > delegation, nor are the machines, as we do not need delegation and don't
> > > > > which to turn it on.
> > > > >
> > > > > When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> > > > > connection and we can see that is it using NTLM for authentication.
> > > > >
> > > > > When trying to attach to SQL Server #2, using TCP/IP, get an error,
> > > > > "Cannot
> > > > > generate SSPI context". When trying to attach using Named Pipes, get a
> > > > > connection and again we can see that it is using NTLM for authentication.
> > > > >
> > > > > SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> > > > > tell) IP Security configured.
> > > > >
> > > > > We are using Active Directory 2003, running in Native Mode.
> > > > >
> > > > > What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> > > > > Server
> > > > > #2.
> > > > >
> > > > > All ideas most welcome
> > > >
> > > >
> > > >
Can anyone get me out of SSPI / Kerberos / NTLM hell ??
This is to do with SQL Server 2005, but I think it is a more general issue.
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcomeAre the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pr...y/tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SP
Ns
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pr...y/tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcomeAre the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:
> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pr...y/tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:
> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SP
Ns
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pr...y/tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>
Friday, February 24, 2012
Can all SS2000 jobs run as owner "sa"?
We have several servers, each with many jobs. Some jobs run as Owner "SA", and some jobs running as various domain admin accounts. We have been told to modify all jobs to run with Owner "SA". Now, some of the jobs read or write flat files and I am a little concerned about this. Does the domain account which starts the SS2000 services determine the rights to read and write files? If so, I guess changing all jobs owners to "SA" has no real risk?
Thanks!
Michael
With respect to accessing external resources, the service account which runs SQL Server Agent requires access rights to those resources for the job to be successful. The owner of the job has no effect on those rights.
HTH.
Thursday, February 16, 2012
Can a Login/Password be validated against a Domain login/passord using a prompt dialog
I have an application database on SQL Server 2K and we've got an NT domain.
I want the users to type in their login and password, but I'd like to
validate that password against the windows NT Domain. Can this be done?
Jared HoffmanHi
This sounds like something that will not be permitted as you could then save
the users account details
You can use integrated security but you don't need a login box, unless you
want a secondary method of verification that you maintain.
John
"Jared Hoffman" <hoffmanj@.kenyon.edu.NO_SPAM> wrote in message
news:edQWKBfDEHA.3804@.TK2MSFTNGP09.phx.gbl...
> I have an application database on SQL Server 2K and we've got an NT
domain.
> I want the users to type in their login and password, but I'd like to
> validate that password against the windows NT Domain. Can this be done?
>
> Jared Hoffman
>|||If the users login to their domain, then they can simply use their nt
credentials to connect.
Change the SQL Server to use Windows Authentication. And change your
application connection string
to make Trusted Connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||My application will be used on computers that may or may not be joined to
the domain so I can't rely on Windows Authentication. I'd like to be able to
authenticate users in such a way I don't have to store another set of user
passwords.
Thanks,
Jared Hoffman
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:pd5X%23SfDEHA.3608@.cpmsftngxa06.phx.gbl...
> If the users login to their domain, then they can simply use their nt
> credentials to connect.
> Change the SQL Server to use Windows Authentication. And change your
> application connection string
> to make Trusted Connections.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||previous post:
"My application will be used on computers that may or may not be joined to
the domain so I can't rely on Windows Authentication. I'd like to be able to
authenticate users in such a way I don't have to store another set of user
passwords.
"
Couple of options:
1. Standard SQL Security
2. Application roles
3. Trusted Security using duplicate nt username and passwords
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I want the users to type in their login and password, but I'd like to
validate that password against the windows NT Domain. Can this be done?
Jared HoffmanHi
This sounds like something that will not be permitted as you could then save
the users account details
You can use integrated security but you don't need a login box, unless you
want a secondary method of verification that you maintain.
John
"Jared Hoffman" <hoffmanj@.kenyon.edu.NO_SPAM> wrote in message
news:edQWKBfDEHA.3804@.TK2MSFTNGP09.phx.gbl...
> I have an application database on SQL Server 2K and we've got an NT
domain.
> I want the users to type in their login and password, but I'd like to
> validate that password against the windows NT Domain. Can this be done?
>
> Jared Hoffman
>|||If the users login to their domain, then they can simply use their nt
credentials to connect.
Change the SQL Server to use Windows Authentication. And change your
application connection string
to make Trusted Connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||My application will be used on computers that may or may not be joined to
the domain so I can't rely on Windows Authentication. I'd like to be able to
authenticate users in such a way I don't have to store another set of user
passwords.
Thanks,
Jared Hoffman
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:pd5X%23SfDEHA.3608@.cpmsftngxa06.phx.gbl...
> If the users login to their domain, then they can simply use their nt
> credentials to connect.
> Change the SQL Server to use Windows Authentication. And change your
> application connection string
> to make Trusted Connections.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||previous post:
"My application will be used on computers that may or may not be joined to
the domain so I can't rely on Windows Authentication. I'd like to be able to
authenticate users in such a way I don't have to store another set of user
passwords.
"
Couple of options:
1. Standard SQL Security
2. Application roles
3. Trusted Security using duplicate nt username and passwords
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Can a Domain Group be set as the dbo to a database?
To keep the Login list more manageable and keep names out of it can I set a
AD Domain Group as the dbo to a database?
This would also help cover the cases when someone goes on vacation and a
different member of the group covers for them.You can add the group to Database Role db_owner.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jim Abel" wrote:
> To keep the Login list more manageable and keep names out of it can I set
a
> AD Domain Group as the dbo to a database?
> This would also help cover the cases when someone goes on vacation and a
> different member of the group covers for them.
AD Domain Group as the dbo to a database?
This would also help cover the cases when someone goes on vacation and a
different member of the group covers for them.You can add the group to Database Role db_owner.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jim Abel" wrote:
> To keep the Login list more manageable and keep names out of it can I set
a
> AD Domain Group as the dbo to a database?
> This would also help cover the cases when someone goes on vacation and a
> different member of the group covers for them.
Tuesday, February 14, 2012
Can 2 SQL servers on domain use same port number?
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must us
e a different port number to the 1st SQL server. Is this the case? It make
s no sense to me and is now
causing a problem with another application we wanted to put onto the SQL ser
ver as well. This program has the port address hard coded, and can not be c
hanged which currently means we can't use it. (The 1st server also has a si
milar piece of software on
it which also can't cope with a different port number, so I can't just switc
h them and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E TaylorYou need different port numbers if you are on the same machine.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must
use a different port number to the 1st SQL server. Is this the case? It
makes no sense to me and is now causing a problem with another application
we wanted to put onto the SQL server as well. This program has the port
address hard coded, and can not be changed which currently means we can't
use it. (The 1st server also has a similar piece of software on it which
also can't cope with a different port number, so I can't just switch them
and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E Taylor|||So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some ne
w
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>|||Yep.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some
new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>|||Thanks - that helps enormously.
Emma
"Tom Moreau" wrote:
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
> So if they are on separate servers, they can have the same port number?
> "Tom Moreau" wrote:
>
> new
> by
>
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must us
e a different port number to the 1st SQL server. Is this the case? It make
s no sense to me and is now
causing a problem with another application we wanted to put onto the SQL ser
ver as well. This program has the port address hard coded, and can not be c
hanged which currently means we can't use it. (The 1st server also has a si
milar piece of software on
it which also can't cope with a different port number, so I can't just switc
h them and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E TaylorYou need different port numbers if you are on the same machine.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must
use a different port number to the 1st SQL server. Is this the case? It
makes no sense to me and is now causing a problem with another application
we wanted to put onto the SQL server as well. This program has the port
address hard coded, and can not be changed which currently means we can't
use it. (The 1st server also has a similar piece of software on it which
also can't cope with a different port number, so I can't just switch them
and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E Taylor|||So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some ne
w
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>|||Yep.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some
new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>|||Thanks - that helps enormously.
Emma
"Tom Moreau" wrote:
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
> So if they are on separate servers, they can have the same port number?
> "Tom Moreau" wrote:
>
> new
> by
>
Can 2 SQL servers on domain use same port number?
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new software onto our domain. They have told us that the 2nd SQL server must use a different port number to the 1st SQL server. Is this the case? It makes no sense to me and is now
causing a problem with another application we wanted to put onto the SQL server as well. This program has the port address hard coded, and can not be changed which currently means we can't use it. (The 1st server also has a similar piece of software on
it which also can't cope with a different port number, so I can't just switch them and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by IP address and then port numbers.
Many thanks
E Taylor
You need different port numbers if you are on the same machine.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must
use a different port number to the 1st SQL server. Is this the case? It
makes no sense to me and is now causing a problem with another application
we wanted to put onto the SQL server as well. This program has the port
address hard coded, and can not be changed which currently means we can't
use it. (The 1st server also has a similar piece of software on it which
also can't cope with a different port number, so I can't just switch them
and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E Taylor
|||So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went by
> IP address and then port numbers.
> Many thanks
> E Taylor
>
|||Yep.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some
new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>
|||Thanks - that helps enormously.
Emma
"Tom Moreau" wrote:
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
> So if they are on separate servers, they can have the same port number?
> "Tom Moreau" wrote:
> new
> by
>
We got in a company to install and configure a 2nd SQL server with some new software onto our domain. They have told us that the 2nd SQL server must use a different port number to the 1st SQL server. Is this the case? It makes no sense to me and is now
causing a problem with another application we wanted to put onto the SQL server as well. This program has the port address hard coded, and can not be changed which currently means we can't use it. (The 1st server also has a similar piece of software on
it which also can't cope with a different port number, so I can't just switch them and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by IP address and then port numbers.
Many thanks
E Taylor
You need different port numbers if you are on the same machine.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must
use a different port number to the 1st SQL server. Is this the case? It
makes no sense to me and is now causing a problem with another application
we wanted to put onto the SQL server as well. This program has the port
address hard coded, and can not be changed which currently means we can't
use it. (The 1st server also has a similar piece of software on it which
also can't cope with a different port number, so I can't just switch them
and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E Taylor
|||So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went by
> IP address and then port numbers.
> Many thanks
> E Taylor
>
|||Yep.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some
new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>
|||Thanks - that helps enormously.
Emma
"Tom Moreau" wrote:
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
> So if they are on separate servers, they can have the same port number?
> "Tom Moreau" wrote:
> new
> by
>
Can 2 SQL servers on domain use same port number?
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new software onto our domain. They have told us that the 2nd SQL server must use a different port number to the 1st SQL server. Is this the case? It makes no sense to me and is now causing a problem with another application we wanted to put onto the SQL server as well. This program has the port address hard coded, and can not be changed which currently means we can't use it. (The 1st server also has a similar piece of software on it which also can't cope with a different port number, so I can't just switch them and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by IP address and then port numbers.
Many thanks
E TaylorYou need different port numbers if you are on the same machine.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must
use a different port number to the 1st SQL server. Is this the case? It
makes no sense to me and is now causing a problem with another application
we wanted to put onto the SQL server as well. This program has the port
address hard coded, and can not be changed which currently means we can't
use it. (The 1st server also has a similar piece of software on it which
also can't cope with a different port number, so I can't just switch them
and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E Taylor|||Yep.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some
new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>
We got in a company to install and configure a 2nd SQL server with some new software onto our domain. They have told us that the 2nd SQL server must use a different port number to the 1st SQL server. Is this the case? It makes no sense to me and is now causing a problem with another application we wanted to put onto the SQL server as well. This program has the port address hard coded, and can not be changed which currently means we can't use it. (The 1st server also has a similar piece of software on it which also can't cope with a different port number, so I can't just switch them and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by IP address and then port numbers.
Many thanks
E TaylorYou need different port numbers if you are on the same machine.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
This might seem very obvious... but my knowledge of SQL 2000 is limited.
We got in a company to install and configure a 2nd SQL server with some new
software onto our domain. They have told us that the 2nd SQL server must
use a different port number to the 1st SQL server. Is this the case? It
makes no sense to me and is now causing a problem with another application
we wanted to put onto the SQL server as well. This program has the port
address hard coded, and can not be changed which currently means we can't
use it. (The 1st server also has a similar piece of software on it which
also can't cope with a different port number, so I can't just switch them
and the old server can't cope with anything more being added to it.)
If they have to have different port numbers, why? I thought traffic went by
IP address and then port numbers.
Many thanks
E Taylor|||Yep.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"KESW" <KESW@.discussions.microsoft.com> wrote in message
news:9735F449-B89B-4224-A71E-447D02225336@.microsoft.com...
So if they are on separate servers, they can have the same port number?
"Tom Moreau" wrote:
> You need different port numbers if you are on the same machine.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "KESW" <KESW@.discussions.microsoft.com> wrote in message
> news:EC1BE223-9D7B-4BC9-8004-414E3079D162@.microsoft.com...
> This might seem very obvious... but my knowledge of SQL 2000 is limited.
> We got in a company to install and configure a 2nd SQL server with some
new
> software onto our domain. They have told us that the 2nd SQL server must
> use a different port number to the 1st SQL server. Is this the case? It
> makes no sense to me and is now causing a problem with another application
> we wanted to put onto the SQL server as well. This program has the port
> address hard coded, and can not be changed which currently means we can't
> use it. (The 1st server also has a similar piece of software on it which
> also can't cope with a different port number, so I can't just switch them
> and the old server can't cope with anything more being added to it.)
> If they have to have different port numbers, why? I thought traffic went
by
> IP address and then port numbers.
> Many thanks
> E Taylor
>
Subscribe to:
Posts (Atom)