January 12, 2012 at 12:15 am
Hi All,
We have a SQL Server 2008 R2 hosted on a server having OS Windows Server 2008 R2.
I am added to Windows, Admin group for the server.
When I am trying to login to the SQL Server using my domain account creadential it is giving an access denied message.
How can I access the SQL server now?
I tried to start SQL Server in single user mode using command line (
sqlservr.exe -c -m). It gave the following error:
2012-01-11 13:50:08.43 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:08.43 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:08.75 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:08.75 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:09.04 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:09.04 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:09.35 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:09.35 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:09.66 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:09.66 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:09.97 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:09.97 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:10.27 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:10.27 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:10.58 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:10.58 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:10.90 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:10.90 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
2012-01-11 13:50:11.21 Server Error: 17058, Severity: 16, State: 1.
2012-01-11 13:50:11.21 Server initerrlog: Could not open error log file ''.
Operating system error = 3(The system cannot find the path specified.).
I confirmed that the log files, data files and error logs are in proper destinations only.
The SQL Server is in started mode.
Can you please help?
January 12, 2012 at 2:29 am
Knowledge Hunter (1/12/2012)
Hi All,We have a SQL Server 2008 R2 hosted on a server having OS Windows Server 2008 R2.
I am added to Windows, Admin group for the server.
When I am trying to login to the SQL Server using my domain account creadential it is giving an access denied message.
How can I access the SQL server now?
....
Ask the DBA to provide you access to the SQL Server.
Adding to Windows Admin group does not grant access SQL Server automatically.
January 12, 2012 at 5:09 am
BUILTIN\Administrators Group might be not added while installation.
January 12, 2012 at 6:34 am
Suresh B. (1/12/2012)
Knowledge Hunter (1/12/2012)
Hi All,We have a SQL Server 2008 R2 hosted on a server having OS Windows Server 2008 R2.
I am added to Windows, Admin group for the server.
When I am trying to login to the SQL Server using my domain account creadential it is giving an access denied message.
How can I access the SQL server now?
....
Ask the DBA to provide you access to the SQL Server.
Adding to Windows Admin group does not grant access SQL Server automatically.
Ditto to this.
But I'm concerned about the output you received. It seems as if SQL can't find its own error log. I'm not sure if that's related to your inability to log into SQL or if it's another issue entirely.
And why would you be trying to set SQL Server to single user mode? Please don't take offense, but given the inability to login into SQL in the first place, an attempt to set the server to single user mode comes across to me as suspicious hacker behavior.
Most DBAs will require you to provide them with a reasonable business usage case for having access to SQL Server, BTW. So you'd best be prepared to give them reasons why you need access and what type of access you need. Just because you get access to SQL Server doesn't mean you'll have the credentials to set the server to single user mode.
January 12, 2012 at 6:38 am
Brandie Tarvin (1/12/2012)
Suresh B. (1/12/2012)
Knowledge Hunter (1/12/2012)
Hi All,We have a SQL Server 2008 R2 hosted on a server having OS Windows Server 2008 R2.
I am added to Windows, Admin group for the server.
When I am trying to login to the SQL Server using my domain account creadential it is giving an access denied message.
How can I access the SQL server now?
....
Ask the DBA to provide you access to the SQL Server.
Adding to Windows Admin group does not grant access SQL Server automatically.
Ditto to this.
But I'm concerned about the output you received. It seems as if SQL can't find its own error log. I'm not sure if that's related to your inability to log into SQL or if it's another issue entirely.
And why would you be trying to set SQL Server to single user mode? Please don't take offense, but given the inability to login into SQL in the first place, an attempt to set the server to single user mode comes across to me as suspicious hacker behavior.
Most DBAs will require you to provide them with a reasonable business usage case for having access to SQL Server, BTW. So you'd best be prepared to give them reasons why you need access and what type of access you need. Just because you get access to SQL Server doesn't mean you'll have the credentials to set the server to single user mode.
Of course, bring that this person has domain credentials in the builtin\administrators may indicate an "accidental dba." 🙂 So, assuming the OP is one... Your domain account needs to be added to SQL Server as a user. You should log in as sa or have someone do that and add you. DO NOT try to use single user mode, that is bad juju.
Jared
CE - Microsoft
January 12, 2012 at 6:58 am
SQLKnowItAll (1/12/2012)
Your domain account needs to be added to SQL Server as a user. You should log in as sa or have someone do that and add you. DO NOT try to use single user mode, that is bad juju.
But if you are an accidental dba, then you should have a domain login with sysadmin permissions (different from the SA account referenced above). I dispute the advice to log in as SA in that it leads to someone using the SA account when they should really leave well enough alone.
As far as the Single User Mode and the SA account use goes, allow me to share a bit of wisdom from a member of my local user group. "If you don't know what it does, don't use it." Learn first, do later. Being DBA is not a job where it's better to ask for forgiveness than permission before flipping switches you don't understand. Believe that.
January 12, 2012 at 7:58 am
Brandie Tarvin (1/12/2012)
SQLKnowItAll (1/12/2012)
Your domain account needs to be added to SQL Server as a user. You should log in as sa or have someone do that and add you. DO NOT try to use single user mode, that is bad juju.But if you are an accidental dba, then you should have a domain login with sysadmin permissions (different from the SA account referenced above). I dispute the advice to log in as SA in that it leads to someone using the SA account when they should really leave well enough alone.
As far as the Single User Mode and the SA account use goes, allow me to share a bit of wisdom from a member of my local user group. "If you don't know what it does, don't use it." Learn first, do later. Being DBA is not a job where it's better to ask for forgiveness than permission before flipping switches you don't understand. Believe that.
I'm confused... A domain login is not the same as an sql user. If builtin\administrators is inactivated as an account on SQL Server, then it does not matter what your domain permissions are. You will have to be added as a user to SQL Server with your domain creds or some other group that you are a member of. In my previous company, the systems admin installed SQL Server and was in control of domain policies and permissions. I did not have admin privileges to the server and had to use sa to configure logins. IF you are a DBA, but because of poor company policies do not have an account with enough access, the only option is sa.
This is my understanding, please correct me if I am wrong.
Jared
CE - Microsoft
January 12, 2012 at 9:30 am
SQLKnowItAll (1/12/2012)
Brandie Tarvin (1/12/2012)
SQLKnowItAll (1/12/2012)
Your domain account needs to be added to SQL Server as a user. You should log in as sa or have someone do that and add you. DO NOT try to use single user mode, that is bad juju.But if you are an accidental dba, then you should have a domain login with sysadmin permissions (different from the SA account referenced above). I dispute the advice to log in as SA in that it leads to someone using the SA account when they should really leave well enough alone.
As far as the Single User Mode and the SA account use goes, allow me to share a bit of wisdom from a member of my local user group. "If you don't know what it does, don't use it." Learn first, do later. Being DBA is not a job where it's better to ask for forgiveness than permission before flipping switches you don't understand. Believe that.
I'm confused... A domain login is not the same as an sql user.
Exactly.
If builtin\administrators is inactivated as an account on SQL Server, then it does not matter what your domain permissions are. You will have to be added as a user to SQL Server with your domain creds or some other group that you are a member of.
Exactly. But I do not advocate logging on with the SA sql user account. I've seen it abused too many times. Since SQL Server does not have a SQL Only Authentication Mode, if the SA account is active, SQL Server is set to Mixed Mode, which allows for domain accounts to be added to the server with the Sysadmin role. This allows for Active Directory integration on a much higher level than a sql user account. And there is no way to turn off the Active Directory constraints of password complexity and changing.
I did not have admin privileges to the server and had to use sa to configure logins. IF you are a DBA, but because of poor company policies do not have an account with enough access, the only option is sa.
There is no need to grant anyone SA login information when the fixed server roles and fixed database roles can be applied to a domain login to accomplish the same tasks. Being made a member of the SecurityAdmin role would have given you the ability to set up logins without giving you a high level account capable of causing great harm on the server.
January 12, 2012 at 9:35 am
Brandie Tarvin (1/12/2012)
SQLKnowItAll (1/12/2012)
Brandie Tarvin (1/12/2012)
SQLKnowItAll (1/12/2012)
Your domain account needs to be added to SQL Server as a user. You should log in as sa or have someone do that and add you. DO NOT try to use single user mode, that is bad juju.But if you are an accidental dba, then you should have a domain login with sysadmin permissions (different from the SA account referenced above). I dispute the advice to log in as SA in that it leads to someone using the SA account when they should really leave well enough alone.
As far as the Single User Mode and the SA account use goes, allow me to share a bit of wisdom from a member of my local user group. "If you don't know what it does, don't use it." Learn first, do later. Being DBA is not a job where it's better to ask for forgiveness than permission before flipping switches you don't understand. Believe that.
I'm confused... A domain login is not the same as an sql user.
Exactly.
If builtin\administrators is inactivated as an account on SQL Server, then it does not matter what your domain permissions are. You will have to be added as a user to SQL Server with your domain creds or some other group that you are a member of.
Exactly. But I do not advocate logging on with the SA sql user account. I've seen it abused too many times. Since SQL Server does not have a SQL Only Authentication Mode, if the SA account is active, SQL Server is set to Mixed Mode, which allows for domain accounts to be added to the server with the Sysadmin role. This allows for Active Directory integration on a much higher level than a sql user account. And there is no way to turn off the Active Directory constraints of password complexity and changing.
I did not have admin privileges to the server and had to use sa to configure logins. IF you are a DBA, but because of poor company policies do not have an account with enough access, the only option is sa.
There is no need to grant anyone SA login information when the fixed server roles and fixed database roles can be applied to a domain login to accomplish the same tasks. Being made a member of the SecurityAdmin role would have given you the ability to set up logins without giving you a high level account capable of causing great harm on the server.
Ok, I'm just saying that if this is a new install and you are the DBA you may HAVE to use sa at least once to set up domain logins for SQL Server if the set up was done by a systems admin or network admin. Stranger things have happened 😛
Jared
CE - Microsoft
January 12, 2012 at 9:39 am
SQLKnowItAll (1/12/2012)
I'm just saying that if this is a new install and you are the DBA you may HAVE to use sa at least once to set up domain logins for SQL Server if the set up was done by a systems admin or network admin. Stranger things have happened 😛
You don't have to use SA to set up domain accounts. In a new install situation, the primary sysadmin account would have been set up already, even if it's just the service account. There are multiple ways around using the SA, especially if you're in Windows Only mode, where SA is disabled.
I'm not saying no one should use SA. I'm saying that I never advise people to give it out or use it if they have no idea what the account is or how it should be used.
EDIT: I should mention that giving out the SA account to anyone who is not Sysadmin on the server should be a big No-No in any DBA's book. I have never met a situation yet that required a non-Sysadmin DBA to have the SA account and *all* its permissions to get his/her job done.
January 12, 2012 at 9:44 am
Brandie Tarvin (1/12/2012)
SQLKnowItAll (1/12/2012)
I'm just saying that if this is a new install and you are the DBA you may HAVE to use sa at least once to set up domain logins for SQL Server if the set up was done by a systems admin or network admin. Stranger things have happened 😛You don't have to use SA to set up domain accounts. In a new install situation, the primary sysadmin account would have been set up already, even if it's just the service account. There are multiple ways around using the SA, especially if you're in Windows Only mode, where SA is disabled.
I'm not saying no one should use SA. I'm saying that I never advise people to give it out or use it if they have no idea what the account is or how it should be used.
I'm just trying to understand, since I have never used Windows Only mode (wouldn't make sense with our application servers being many different operating systems). So what if a network admin does the install under their own domain account and they have builtin\administrators disabled? How would I get access? I would have to log in as local admin to the server or have the installer add my account? Is that correct?
Jared
CE - Microsoft
January 12, 2012 at 10:02 am
SQLKnowItAll (1/12/2012)
Brandie Tarvin (1/12/2012)
SQLKnowItAll (1/12/2012)
I'm just saying that if this is a new install and you are the DBA you may HAVE to use sa at least once to set up domain logins for SQL Server if the set up was done by a systems admin or network admin. Stranger things have happened 😛You don't have to use SA to set up domain accounts. In a new install situation, the primary sysadmin account would have been set up already, even if it's just the service account. There are multiple ways around using the SA, especially if you're in Windows Only mode, where SA is disabled.
I'm not saying no one should use SA. I'm saying that I never advise people to give it out or use it if they have no idea what the account is or how it should be used.
I'm just trying to understand, since I have never used Windows Only mode (wouldn't make sense with our application servers being many different operating systems). So what if a network admin does the install under their own domain account and they have builtin\administrators disabled? How would I get access? I would have to log in as local admin to the server or have the installer add my account? Is that correct?
The scenario you describe is what would happen in a business where no one knows anything about SQL and SQL Security (and possibly little about Windows / network security as well). In that scenario, I would do 3 things.
My first question would be "Why is a network admin installing SQL Server?"
My second question is, "Why were the service accounts installed under your personal login instead of a plain vanilla domain login?"
My next action would be to write a long letter to my boss (and potentially CC the network admin's boss) about the dangers of tying in SQL Server service accounts with a user's login. Then I would convince them to allow me to uninstall and reinstall SQL Server correctly with the correct security setups.
Because this is someone else's thread, I won't answer every security question you have here (if you want to post a new thread and point me to it, I'll be happy to write chapter and verse there). I can give you a piece of advice, though. Buy a copy of SQL Developer Edition (I believe Amazon has it for $50.00) and install it on a laptop or PC (something you don't have to worry about breaking) so you can see how the installs work. Then download a copy of Books Online and read up on security.
January 12, 2012 at 10:26 am
In this OPs case, since we know nothing from them yet really, I would venture to say that the errors occurred because the service was already started. OP cannot access SQL Server because either they are NOT part of builtin\administrators group as they think, or bultin\administrators group has been denied access to SQL Server login.
OP, are you DBA? Developer? Just someone who needs access? Why are YOU the one posting the question as opposed to someone else? Just trying to get at the real issue here to help you the best.
Jared
CE - Microsoft
January 13, 2012 at 4:52 am
My understanding is that I will ahve to get access for the SQL Server from someone who already has access to the SQL Server.
Thanks everybody for your inputs.
January 13, 2012 at 4:57 am
Knowledge Hunter (1/13/2012)
My understanding is that I will ahve to get access for the SQL Server from someone who already has access to the SQL Server.
Yes, you will. Is there someone in your company that already has access? Or are all people with access gone / left the company?
Thanks everybody for your inputs.
You're welcome. My apologies for hijacking your thread with the other conversation.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply