Changing from Windows Authentication to Mixed Mode | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Changing from Windows Authentication to Mixed Mode

Hello When I installed SQL Server 2005 Express server on a laptop, I set the service to use Windows Authentication. Now that it is already installed, I would like to make it possible to connect to the server from another machine using SQL Server Authentication -that is, using the "sa" user with a certain password. How can I add this new type of authentication to the server? Thanks a lot.
hi,
you cant add a new authentication. you will have to change authentication mode from windows to mixed. AKTHAR
steps to do:
in management Studio- connect to your server database service using windiows authentication.
right click your server and properties- in connection change to sql and windows. AKTHAR
forgot to add:
to change sa password.
open security and logins- right click sa – enable the accoung and change the password AKTHAR
quote:Under Windows Authentication, each server instance logs in to the other side using the Windows credentials of the Windows user account under which the process is running. For this reason, Windows Authentication requires that SQL Server services must run as domain users in trusted domains or as network services. To authenticate both ends of a connection, Windows Authentication uses the credentials of the Windows user account on which the SQL Server instances are running. Therefore, the user account of each server instance must have the permissions needed to log in and send messages to each of the other server instances.
So if you have that user credentials associated on this express instance then you could be able to connect without changing any authentication.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
SQL Server 2005 Books Online
How to: Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670(SQL.90).aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

thanks for your reply. I tried to connect to a server that now accepts Mixed mode and sqlcmd fails to connect. This service accepts remote connections. What I did first:
1) Using the Server Properties, in the Security Section, I changed from Windows to Mixed Mode.
2) I restarted the service.
3) I changed the password of the "sa" user.
4) I enabled the "sa" user.
5) Using the configuration tool, I enabled the TCP/IP protocol.
6) I restarted the service. I tried using sqlcmd on the same computer and it connects (the same with the Management Studio). However, from a remote computer I cannot connect: >sqlcmd -SRT00415SQLEXPRESS -Usa -P1234 using sqlcmd -L does not display the RT00415 SQL service. What is missing?
What error you are getting with SQLCMD when trying to connect that remote instance? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks for you reply. Here is the message: HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
quote:Originally posted by EMoscosoCam thanks for your reply. I tried to connect to a server that now accepts Mixed mode and sqlcmd fails to connect. This service accepts remote connections. What I did first:
1) Using the Server Properties, in the Security Section, I changed from Windows to Mixed Mode.
2) I restarted the service.
3) I changed the password of the "sa" user.
4) I enabled the "sa" user.
5) Using the configuration tool, I enabled the TCP/IP protocol.
6) I restarted the service. I tried using sqlcmd on the same computer and it connects (the same with the Management Studio). However, from a remote computer I cannot connect: >sqlcmd -SRT00415SQLEXPRESS -Usa -P1234 using sqlcmd -L does not display the RT00415 SQL service. What is missing?
Provide the exact error what you are getting… Try first with instance and port number some times it can’t be determine dynamically…
Ex: sqlcmd -SRT00415SQLEXPRESS,portnumber -Usa -P1234
If the above solution didn’t work then
try after enabling the remote connections…. in express edition by default it disabled….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Thanks a lot. That is the error message, as displayed on the command prompt. In the Connections Section of the Properties of the server, the "Allow remote connections to this server" checkbox is marked. However, the "Configured Values" option button is marked, but I ignored if this affect in anything. I also changed the option button so that "Running values" is selected instead and later on I restarted the service. But it did not work either.
is native client enable on your sql server? AKTHAR
Yes. Both machines have SQL Server 2005 Express SP2, and in both the Management Studio Express is able to connect to the service on the same workstation; TCP/IP is enabled (using then SQL Configuration Manager).

]]>