How to change windows authentication mode to sql server authentication mode.

searchsanjaysharma
searchsanjaysharma used Ask the Experts™
on
I have sql server 2008 management studio express. Its in Windows authentication mode. How do i set to sql server authentication mode.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan McCauleySenior Data Architect

Commented:
If you want to connect to a server using SQL Authentication mode, just select it in your new connection dialog box instead of "Integrated Authentication".

If you want to set a server to accept SQL Authentication and it currently doesn't, you'll have to connect to the server using Windows Authentication (and a user that has the Server Admin role), and in the properties of the sql instance, you can change the authentication mode allowed.

Author

Commented:
I think you havent understood the question.
I want to reset my mode to sql server authentication mode.
and set password for sa
i have changed the security to Windows and Sql Server authentication mode.
How how to set the passwod of sa
alter user sa enable
alter user sa enable with password='admin12'
Is it ok
Ryan McCauleySenior Data Architect

Commented:
The two options are "Windows Auth" and "Windows or SQL Auth" - there's no option that allows just SQL logins, so you'll have to leave it on the current "Allow both" option if you want SA to work.

Otherwise, yes - just enable the SA account and reset the password (which is likely blank) and you should be good to go. Especially since the account was previously disabled, there are no side-effects to doing that.

Author

Commented:
I think you have not understood the question, the taskyou are telling is very simple and any1 can do that.
I am asking for commands.
to active sql server authentication.
Senior Data Architect
Commented:
Are you asking for a walk-through like this one?

http://kbase.gfi.com/showarticle.asp?id=KBID002804

Or if you're asking how to do it using T-SQL, there's not really a direct way. You have to modify registry keys, which you can do using a SQL Script, assuming the right server access is enabled (which it likely isn't):

http://www.dbforums.com/microsoft-sql-server/828267-enabling-mixed-mode-auth-tsql.html

If this doesn't address your question, then you're right - I don't understand. If you need more help, can you please clarify?

Author

Commented:
ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial