Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dvdljns
Starting Member
2 Posts |
 Posted - 2011-02-27 : 16:29:20
|
| sql server 2008 r2 on windows 7 ultimate. When I install sql server it insists on using my windows acount as a admin password. I need to find a way around this. Can I set up an account on windows with no permissions and set that account as my sql admin password?? I can not believe they broke sql like this. Now if you make an available over the web you are giving script kiddies access to your account password. Doesn't microsoft have anyone working security any more. I need a way to devorce sql from my windows accounts. There has to be away to set up an sql admin account with out tying it to a windows account. otherwise I might as well just publish my windows account on my web page. has anyone figured out how to handle this yet?? |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2011-02-28 : 13:38:09
|
| First, you don't make it available over the web. You pass parameters to an application or script on your web site that connects to the database and then returns the results in a web page.Second, you can use integrated security instead of Windows security. Read Books OnLine for info.Last but not least, posting a totally unformatted rant about Microsoft's security decisions doesn't accomplish anything. While it may make you feel better, it makes it hard to read your question, and the majority of the time what you're ranting about isn't the real problem. (Which is so in this case - the problem you're having has nothing to do with MS security, but your own lack of knowledge about how web sites should be designed and how to configure SQL Server.) And at least use paragraphs, please. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-28 : 13:55:16
|
What Ken said, except:quote:
Second, you can use integrated security instead of Windows security.
"Windows" security is integrated security. I think he meant SQL authentication. quote:
When I install sql server it insists on using my windows acount as a admin password
It does not insist, in fact it does not even default to this, you'd have to manually enter a domain or Windows login and password. You can use either LocalSystem or Network Service accounts to run the SQL services with minimal privileges.quote:
Can I set up an account on windows with no permissions and set that account as my sql admin password
Absolutely, but it's no different from using LocalSystem.quote:
There has to be away to set up an sql admin account with out tying it to a windows account.
There is, it's called "sa", and you should NEVER use it, nor should you ever use an administrator-level login for any application. Set up logins and user accounts as you described for Windows, with minimal permissions necessary for the tasks they perform. |
|
|
|
dvdljns
Starting Member
2 Posts |
Posted - 2011-03-02 : 02:04:28
|
Ok! Since as you pointed out I really do not know much about this. I am going to assume you are right. I will reload sql 2008 r2 and maybe You can help me figure out why I can only log in using my windows password.quote:
It does not insist, in fact it does not even default to this, you'd have to manually enter a domain or Windows login and password. You can use either LocalSystem or Network Service accounts to run the SQL services with minimal privileges.
Actually that is exactly what it was doing. it only gives me two choices windows authentication and mixed mode but when I used mixed mode it copied my administrator login and password over to the database. the only password that it would setup and use outside that is a user account with what ms 7 calls a standard account. Which still has more permission then I want anyone to have. " assumiming they break in"When I set up my email server I had to use my admin login to connect to the sql db. Which means that anyone who broke the db password would have my admin password for the network.quote:
There is, it's called "sa", and you should NEVER use it
Why? I start out by assuming somebody can break in and if they only get access to my db, It limits the damage. In linux I create a login with no permissions. "what I call an empty account" It has no read, no write no nothing. then set up permissions in mysql for it. will sa do that? quote:
Set up logins and user accounts as you described for Windows, with minimal permissions necessary for the tasks they perform.
Since sql is a server I see no reason for it to have any rights on windows. or visa versa.I have not figured out how to give less then standard privaliges to a windows 7 account and get it to work. I think the problem is the way everyone looks at ths whole integration thing. I do not see sql as a partner with windows but as a traiter just waiting to give access to windows. All this switching users with out rebooting and accessing admin from a user account may make it easier to work on windows but it also gives better access to your comp from outside. I am ranting again but it is frustrating to me. Can I setup sql 2008 to where windows will treat it as a seperate server? and if I can will you help me. I have disabled all remote logins I can find in windows. Everything else on here that calls itself a server I would like to setup as dead ends. Meaning in this case that if you break an app and work your way back to the sql server, you only have rights to the sql server. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-02 : 07:11:32
|
I misworded/badly worded some of my earlier replies, sorry for the confusion. Let's rephrase:- Any reference to "sysadmin" or "administrator" privileges refers ONLY to SQL Server, not Windows.- You do not have to use Windows authentication to access your SQL Server. You can use SQL logins that have no Windows permissions. See below.- Regarding Windows Auth and it automatically adding your Windows account: I mistook your earlier post to mean the Service account under which SQL Server runs. In your follow-up you're talking about adding your current account for sysadmin access to SQL Server. This is a default practice, BUT it is restricting sysadmin access to ONLY those accounts in the list. Without it you can lock yourself out of administering your SQL Server. Granted that's the best security of all, but not particularly useful. It's also no more of a security loophole than the root user on a Linux system.- SQL logins are completely separate from Windows, they exist only in SQL Server. These are created manually after installation. As you mentioned, you create these logins with minimal permissions/privileges.- The only SQL login than can be created at install time is sa, which has automatic sysadmin privileges. Set a strong password for this, but NEVER use sa except as a last resort/dire emergency. If you're still concerned you can disable the sa login, just remember to have another sysadmin-level login available. Again this is no different than root user, with the same caveats.- There are other options for security, including application logins and certificate-based logins. These are a little more complicated to set up, but once you get used to regular SQL auth you can explore these options.quote:
I do not see sql as a partner with windows but as a traiter just waiting to give access to windows
Stop. I agree it's frustrating but this kind of thinking precludes help. If you get stuck, ask questions. |
|
|
|
|
|
|
|
|
sql server password