| Author |
Topic |
|
RyanAustin
Yak Posting Veteran
50 Posts |
 Posted - 2007-06-12 : 13:40:27
|
| I have a SQL2005 in a cluster environment, for some reason the only way that user accounts can login to either the database or SSMS is to grant them the SysAdmin role. This access is a little to high for my liking and am wondering if anyone else has come across this before. Thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-12 : 13:43:38
|
| What is the error that they are receiving?We've got a 4-node cluster running 11 SQL Server 2005 instances and don't have any issues with non-sysadmins connecting.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-12 : 13:59:04
|
| I'm seeing the following in the error logs:Date 6/11/2007 10:59:23 PMLog SQL Server (Current - 6/12/2007 11:55:00 AM)Source LogonMessageLogin failed for user 'alarmpoint'. [CLIENT: 10.1.5.229]Date 6/11/2007 10:59:23 PMLog SQL Server (Current - 6/12/2007 11:55:00 AM)Source LogonMessageError: 18456, Severity: 14, State: 16.---------------------------------------------------------------------I'm also seeing the following for the port information:Date 6/11/2007 10:59:23 PMLog SQL Server (Current - 6/12/2007 11:55:00 AM)Source spid17sMessageError: 26023, Severity: 16, State: 1.Date 6/11/2007 10:59:23 PMLog SQL Server (Current - 6/12/2007 11:55:00 AM)Source spid17sMessageServer TCP provider failed to listen on [ 10.1.5.9 <ipv4> 1433]. Tcp port is already in use.Ryan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-12 : 14:09:56
|
| That message about the port is not good. Have you tried changing the port SQL Server is listening on? Are the users actually seeing the Login Failed error message when they try to login?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-12 : 16:16:21
|
| Yes they are getting Login failed for user 'alarmpoint'. (Microsoft SQL Server, Error: 18456)I did just change the MSDTC from the cluster to the cluster resource and that has changed the port of the Named Pipes from 1433 to 1434 and I still can't login. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-12 : 16:47:28
|
| I don't understand why you touched MSDTC. Could you explain that? Was MSDTC working, meaning can it fail to the other node successfully? Are you able to run a distributed transaction?Login failed means one of two things only. Either the user does not exist or the password for the user is incorrect.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-12 : 16:54:10
|
| One of our Server Admins didn't agree to the initial setup of the cluster so we changed it. As for the login I know its for a fact its valid cause I can connect using Named Pipes, but not TCP/IP. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-12 : 17:10:00
|
| If they connect via this: tcp:VirtualServerName\InstanceName,PortNumberDoes it work? If it does, then...Setup an alias on the client machines using SQL Server Configuration Manager, making sure to select the port that SQL Server is listening on in the alias. Have the alias be for TCP/IP.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-12 : 17:47:05
|
| When I try using TCP:CD1001-D04V,1433 I get the following:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider).I have made sure that remote connections is allowed.Thank you, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-12 : 17:52:43
|
| Since you are receiving that error about 1433 already being in use, you need to change the port that SQL Server listens on, restart SQL Server, then try connecting with this new port.You also should figure out what is using 1433 on the server already.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-12 : 20:38:44
|
| How many instances do you have on the cluster? By the way, don't use port 1434 since it's sql management port. |
|
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-13 : 11:42:06
|
| There is one SQL instance currently. During the night, I did try to change the port to 1533, but because the SPN wasn't registered it actually caused the cluster itself to fail and I had to revert back to 1433. I have registered the SPN on our domain using the setspn Resource Kit:MSSQLSvc/cd1001-d04v.corp.ads:1434MSSQLSvc/cd1001-d04v.corp.ads:1433Also, I am noticing that our domain account that controls the cluster is logging into SQL2K5 but there is no login information setup under security, is this normal?Thank you |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-13 : 12:48:28
|
| Is it member of local administrators group? |
|
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-13 : 12:53:16
|
| Yes it is |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-13 : 21:51:49
|
| Cluster service account will connect to sql once sql resource starts, it gets permission via domain groups created for sql cluster installation. |
|
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2007-06-20 : 15:51:01
|
| After weeks of struggling and fighting, I found that the problem was the Service Broker that I created. It was listening on port 1433. I dropped it and recreated it and everything is working fine now, well except messaging, I have to fix that problem now. Thanks everyone for thier input.Ryan |
|
|
|
|
Non SysAdmin accounts cannot login