February 24, 2010 at 12:14 pm
We've got the server up and running with SQLServer Express. When I try loggin in using the 'sa' account, I'm locked out. I've tried leaving the password blank as well as using 'sa' as the password. Its a fresh install and only myself and one other person have access to it. I did change the authentication in the dropdown on the login screen from Windows Authentication to SQL Server Authentication.
I even had our admin login to the server directly and tried loging in to SQL Server as 'sa'.
February 24, 2010 at 12:47 pm
You canot have only SQL Auth, you can have mixed mode, which is Windows and SQL Auth. Log into the database using Windows Authentication. Of log into Windows as someone with Admin permissions and log into SQL using Windows Auth, and verify that the Instance is set to Mixed Mode. Then set the password of the sys admin account, to something that you know.
Or you can create a new account and assign admin permissions to it. You do not have to, and actually you should not be using the sys admin account. Never a good idea.
Andrew SQLDBA
February 24, 2010 at 1:24 pm
I think that the admin may have setup a password for the sa account when he installed SQLServer on the machine.
Q: Which is preferred Windows Authentication or Windows/SQLServer (mixed)?
Q: Which is easier to maintain for someone with zero experience as an Admin? (Even temporarily 6mos - 1 year)
February 25, 2010 at 10:26 am
david.holley (2/24/2010)
Q: Which is preferred Windows Authentication or Windows/SQLServer (mixed)?(Even temporarily 6mos - 1 year)
Windows Authentication is Microsofts answer...and mine.
david.holley (2/24/2010)
Q: Which is easier to maintain for someone with zero experience as an Admin? (Even temporarily 6mos - 1 year)
Windows Authentication, but you will still need some knowledge of SQL Server roles. BOL actually has fairly good sections on this...that are actually in English :w00t:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 25, 2010 at 11:50 am
Again, the old answer..... It depends
For a website app, SQL Auth with a login that has no permissions. For a users login, Windows Auth. I mean, you are not going to have but a couple users log in to Express. Not like you can have a huge number. If you an admin on the box where Express resides, log in and reset the "sa" password to what ever you want. You need at least one admin for SQL. How would you ever think of creating another user?
Yes, I agree.... Read the SQL Books Online. They tell you all that you wanted to know about SQL Server, covers some of Express as well
Andrew SQLDBA
February 25, 2010 at 12:21 pm
I was figuring that Windows Authentication was the best route. I've been using SQLExpress on my personal laptop and didn't consider the ramifications of trying to set it up in a networked environment where I'm not God.
Also, it wasn't until hours after the initial post that I figured out that our LAN Admin reset the sa password without telling me when he did the install.
I did just buy a Bible to puruse and get my life as a wannabe SQL Server Administrator in order.
February 25, 2010 at 1:27 pm
Use the SQL Book Online, much better, much lighter weight, and the other thing......
Oh yea, Free.
You should not allow someone else to install SQL for you. How do you know they configured it correctly? Already you are having problems, that should give you a clue. I almost bet they installed it on the Boot Partition. I would never mix the OS and the database App on the same partition.
But this being Express, you do not have to worry too much about a ton of people hitting it, or the database getting large at all.
Andrew SQLDBA
February 26, 2010 at 1:11 pm
Problem solved.
The Server Admin had provided a password for the 'sa' account which I was not aware of. Once I got the password, I made the mistake of logging in remotely using an ID that is not an administrator on the server. I changed the authentication from MIXED to WINDOWS which I was able to do, however doing inadvertenly locked everyone out of SQLServer since no Logins had been created under SQLServer. I'm guessing that when SQLServer is in MIXED, you have to manually add users, whereas WINDOWS will do it automatically. In the end we uninstalled/reinstalled to start from scratch.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply