August 16, 2010 at 7:36 am
Just installed SQL Server 2005 (and SP3) onto a new Windows 2008 R2 server. The Windows machine does have a ton of policies/procedures lock-downs. I'm not sure if that relevant.
I can login into SSMS with the service account that runs the SQL service, but I cannot login with my individual id using Windows Authenication. My id is in an Active Directory group called "DBAs". That AD group is in the local "Administrators" group on the machine. Since I have not yet deleted "BUILTIN\ADMINISTRATORS", I should be able to gain access via SSMS.
When I add the group "DBAs" to the SQL Server, I can gain access. Therefore, does anyone know if there is a problem with "groups within groups" on SQL Server 2005 when running Windows 2008 R2?
Thanks,
Cindy
August 16, 2010 at 9:14 am
Starting SP3, Microsoft (finally) fixed the security issue. Even if you are part of BUILTIN\Administrators, you will not be granted SYSADMIN on SQL Server (yeeeh!). So you should instead remove BUILTIN\Administrators and add your DOMAIN\SQLDBA group instead.
In SQL 2008, BUILTIN\Administrators doesn't even get added any more :D.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 16, 2010 at 12:55 pm
I think there may be more to the story than builtin\administrators not working at all in SP3. There are several SQL Server 2005 servers installed here running SP3 that are accessed via builtin\administrators. They weren't built by me and I don't support them, but I know that's how I log into them. I just verified that.
Likewise, I'm not going to discount your statement either, because I do believe this could clearly be the case on the new server I just installed. Since I don't need (or want) builtin\administratrors, I have deleted it and added the AD group that I want. If this truly wasn't the problem, I'll cross that bridge when I get there. In the meantime, I'm not going to continue researching a problem for a "feature" I don't even want to use.
Thank-you for your input and fresh perspective.
Cindy
August 16, 2010 at 2:27 pm
If this is a new build then it makes sense because you might have noticed right after SP3 install was finished it would have brought up the provising tool. Where was in already existing installs security was all ready configured. So installing SP3 on it didn't hurt it. Plus its another wonderful thing in Windows 2008, that doesn't exist in Windows 2003.
I ran into issue with Reporting Services, where even though I was part of local administrator group I could not administer the service at http://servername/reports/. After looking at it I figured out if I run the browser with "Run as Administrator" option then proper security tokens were passed into report server for me to authenticate, without it it with held some security tokens which allow for authentication.
This has to do with the UAC, implemented in Windows 2008.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply