how can associated 'sa' with a trusted SQL Server connection in sql server 2005?

I want to use sa user login sql server 2005 to visit my database "dotnet20" but when I set the user property in User Mapping, It Report 

when I set it, An Error Occur like follow
Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)
and also when I want to Login sql server use 'sa' user, It Report
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
how can  associated 'sa' with a trusted SQL Server connection?
0
idiot
11/23/2005 6:17:32 AM
📁 asp.net.sql-datasource
📃 29906 articles.
⭐ 0 followers.

💬 6 Replies
👁️‍🗨️ 1046 Views



I'm having this exact same problem.
When I login using Windows authent. I can connect to the DB but cannot ad users or grant permissions.
I can create tables fine, but other than that not much? Any idea.
I basically wanted to enable SQL Authent. as well as windows in the security Tab after right-clicking on my Database, but the Windows login lacks the rights although it was used to create the DB and all.
Help !

"Our truest life is when we are in dreams awake." - Henry David Thoreau 
0
BugSlayer
3/9/2006 7:11:00 PM

Basically, how can I add my Windows user account to the sysadmin role.
I couldve used the default sa account but whenever I login in SQL Serv authent. mode using 'sa' and blank Pwd on the SQL Serv Mngmt Studio Express CTP, on the 1st attempt, it says smthg. like
Cannot connect to INSPI6K\SQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
Upon trying again just says
Cannot connect to INSPI6K\SQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
 
when I changed the Network protocol to Named Pipes or TCP/IP  I got
Cannot connect to INSPI6K\SQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
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. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)
 
What's happening? Why can't I login as 'sa' ???
Please tell me all U  DBA-gods out there...

"Our truest life is when we are in dreams awake." - Henry David Thoreau 
0
BugSlayer
3/9/2006 7:58:19 PM

One f the suggestions I recvd. was to hack the registry, and change the LoginMode of the SQL Server 2005 Express.
Any idea how to go about it, and which Reg. key to change for enabling mixed authentications(SQL & Windows), instead of just Windows auth.?
I saw this article but couldn't find that key
 http://support.microsoft.com/default.aspx?scid=kb;en-us;285097
Thanks

"Our truest life is when we are in dreams awake." - Henry David Thoreau 
0
BugSlayer
3/10/2006 1:33:58 PM

OK I finally found the LoginMode key by searching the Registry and changed it to 2 (original value was 1)
Required a Restart, for it to take effect.
Thankfully not getting the trusted connection error anymore.
But now it's whining about the password.
AFAIK I never set any Pwd for 'sa' account, yet executing sqlcmd on command prompt throws error
Password Msg:18456
Is there any way to reset the sa PWD??? What's the way out?

"Our truest life is when we are in dreams awake." - Henry David Thoreau 
0
BugSlayer
3/10/2006 2:41:59 PM

Phew! The problem has finally been resolved.
What a nightmare... all due to installing SQL Server 2005 Express on top of existing MSDE (from VS 7.0)
See http://forums.asp.net/1222469/ShowPost.aspx for further details.
and with some help from this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124596&SiteID=1
Thanks.
Moral of the story: Always unninstall previous versions. Better safe than very sorry !

"Our truest life is when we are in dreams awake." - Henry David Thoreau 
0
BugSlayer
3/14/2006 3:40:10 PM

(It might not be relevant to this post But I reached here googling the solution for 'Login Failed for the user 'sa' . So after I found the solution I thought to share it here for those like me.)
There are two reasons bcoz of what we cant login through sa after installing fresh SQL Server 2005 :

Reason 1- 'sa' is "disabled" by default
Reason 2-'Server Authentication' is set to "Windows Authentication Mode" by default


We must enable sa And set Server Authentication to "SQL Server and Windows Authentication Mode"  to login through sa.

To enable 'sa', follow these steps


1- First Connect to SQL Server using Windows Authentication.
2- Expand Security folder in Object Explorer (in left side pane).
3- Then expand Login Folder and locate the 'sa' user. Right click sa and open Properties.
4- Click 'Status' in the left pane of properties window.
5- You'll see Login options in the right pane. Just select Enabled and click OK to exit properties window. sa is now enabled.
6- (Also in the 'General' page of these properties, erase the text in Password and Confirm password text boxes; and uncheck the Enforce password policy checkbox.. This will let you login through 'sa' with empty password.)

Now to  Change Server Authentication, do the following


1- In Object Explorer: Right click Your Server and open Properties
2- Select Security in the left pane of the properties window
3- On the right side you will see Server Authentication
4- Select  "SQL Server and Windows Authentication Mode"
5- Close the properties windows by clicking OK
6- Restart your Server by right clicking your Server and choosing restart
Now you can login through 'sa'
0
anwaar
3/9/2009 9:40:21 AM
Reply: