February 2, 2010 at 12:01 pm
I know in SQL 2008 if you don't add a windows login\group during install you can get locked out if you don't configure a SQL login, the builtin administrators group does not have default sysadmin rights as in previous versions. Found myself in this position on an install and started the SQL services using my domain account for startup credentials via configuration manager and was able then to get access to SQL and add the requisite domain groups\accounts and then changed the services to use the designated domain account as originally setup.
Andrew
February 3, 2010 at 4:19 am
Isn't there a way of preventing SQL Server 2005 from let someone connect as sinle user?
I mean , can we tell to SQL Server that no one can connect as sinlge user?
Thank you.
February 3, 2010 at 7:21 am
river1 (2/3/2010)
Isn't there a way of preventing SQL Server 2005 from let someone connect as sinle user?I mean , can we tell to SQL Server that no one can connect as sinlge user?
Thank you.
To start SQL Server in single-user mode, you have shutdown the service and then restart it from the command-line using the correct switch. This requires access to the server running SQL Server, either physical access or via RDP.
February 3, 2010 at 7:25 am
Yes, but in my case that's a problem.
February 3, 2010 at 7:29 am
river1 (2/3/2010)
Yes, but in my case that's a problem.
Don't follow. You would only do this if you lost admin access to MS SQL Server as the OP did. This is not something you'd do normally.
February 3, 2010 at 7:36 am
take a look at this:
http://qa.sqlservercentral.com/Forums/Topic857013-146-1.aspx
you will see my problem.
I have people (in a client company) that are Domain admins and we don't want them to access the SQL Server instance that we have installed.
From what you wrote i have a problem because of this...
Can you please read the link so i can put you some problems that i have? maybe you can help.
Thank you
P.S - do you Master know this?
http://qa.sqlservercentral.com/Forums/Topic858526-146-1.aspx
February 3, 2010 at 7:55 am
river1 (2/3/2010)
http://qa.sqlservercentral.com/Forums/Topic857013-146-1.aspx
you will see my problem.
I have people (in a client company) that are Domain admins and we don't want them to access the SQL Server instance that we have installed.
From what you wrote i have a problem because of this...
Can you please read the link so i can put you some problems that i have? maybe you can help.
Thank you
P.S - do you Master know this?
http://qa.sqlservercentral.com/Forums/Topic858526-146-1.aspx%5B/quote%5D
Does the client own the hardware your software is installed on? Do the support the underlying OS? Who provides DBA support for the SQLS erver instance?
February 3, 2010 at 8:03 am
They own the server. they do the windows maint. tasks.
We should only worry about the SQL Server instance .
Our management of the instance is made remotly, mostly by scritps.
February 3, 2010 at 8:04 am
Do you master make any ideia of this:
http://qa.sqlservercentral.com/Forums/Topic858526-146-1.aspx
thank you
February 3, 2010 at 8:11 am
river1 (2/3/2010)
They own the server. they do the windows maint. tasks.We should only worry about the SQL Server instance .
Our management of the instance is made remotly, mostly by scritps.
Monitor the SQL Server logs. If SQL Server is taken down and restarted in single user mode, this is where you will find this. Not sure off the top of my head, but there should be a way to check the logs using scripts. Search SSC, and maybe start another thread asking this.
February 3, 2010 at 8:17 am
Not sure you can read the error log with T-SQL. It's just a text file, so you can scan it with FIND, TAIL or other command line utilities. The downside of assuming you can read the error log looking for a restart in single user mode is that someone could restart the server 6 times, and recreate all the logs.
In terms of preventing someone from accessing SQL Server if they are a domain admin, you can remove BUILTIN/Administrators, but you'll want to be sure you have other sysadmin accounts in there. They'll have to be SQL auth accounts or the domain admins can always get around things.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 3, 2010 at 8:24 am
For reading the error log you can use the following:
Exec master.dbo.xp_readerrorlog 1, 1, 'search string1', 'search string2'
The first parameter (as I am sure you know) is the log number, the second is the type of log (1 = SQL Server, 2 = SQL Server Agent) and the third and forth parameter are search strings with a conditional "and" so both criteria need to be met. So if you wanted to search for Traceon events you could use this:
Exec master.dbo.xp_readerrorlog 0, 1, 'TraceOn’
I am not sure offhand what any entries would be to indicate the server was started in minimal configuration mode. Also, like Steve said, they could always cycle the log to the point where it is no longer recorded.
February 3, 2010 at 8:25 am
Steve Jones - Editor (2/3/2010)
Not sure you can read the error log with T-SQL. It's just a text file, so you can scan it with FIND, TAIL or other command line utilities. The downside of assuming you can read the error log looking for a restart in single user mode is that someone could restart the server 6 times, and recreate all the logs.In terms of preventing someone from accessing SQL Server if they are a domain admin, you can remove BUILTIN/Administrators, but you'll want to be sure you have other sysadmin accounts in there. They'll have to be SQL auth accounts or the domain admins can always get around things.
True, Steve. Since this is in a SQL Server 2005 forum, I wonder if there are any events that could be used to capture necessary info to alert/audit such activities. Of course, locks only keep honest people out.
February 3, 2010 at 8:49 am
So, i must assume that there is no way of preventing the domain admins of a server , even if they do not have access inside SQL Server to restart it and take ownership of SQL Server...
As to reading logs... i'm not certain that it would be good i was thinking in do things in other form...
But we have a point (they can take ownership if they are domain admins).
If they are not, they can not do this, correct?
Maybe i can talk to the client and tell that the SQL server Servers should not have the domain admin group. It's a possibility. This way they will not be able to do anything.
What about the question that i was asking?
I have configurated the SQL Server through configuration manager to use the "Local Service" as the account that runs the SQL Server Agent service.
Now i want to give permissions to this specific account on SQL Server 2005, when i try to do this 2 account look the same, they are:
NT AUTHORITY\SERVICE and NT AUTHORITY\LOCAL SERVICE
That are the diferences between this two accounts? and what is the account that corresponds to the "Local Service" that i gave via configuration manager?
February 3, 2010 at 8:53 am
Ultimately, no, there is no way to prevent a domain admin from accessing SQL Server. If the client owns the physical box, then they can get to everything. Not sure you even have a legal right to prevent that.
What you can do is write a contract that says they won't access the SQL Server without you present, or aware. That would protect you. Ultimately if it's their server and their data, they have the right, and ability, to get to the data.
As to the accounts, not sure. Local Service is recommended for running SQL Server when it does not need access to anything outside the host computer. I'm not sure what "service" is. There's a "system" account, and Local System, as well as Network Service. What OS is running?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply