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?

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 !

|||

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...

|||

One f the suggestions I recvd. was to hack the registry, and change theLoginMode 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

|||

OK I finally found theLoginMode 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 thetrusted 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?

|||

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)

Seehttp://forums.asp.net/1222469/ShowPost.aspx for further details.

and with some help from this articlehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124596&SiteID=1

Thanks.

Moral of the story: Always unninstall previous versions. Better safe than very sorry !

HostName and ReinitializeSubscription

Hello all,

I'm having trouble with my Merge Replication setup in regards to changing the HostName property of the SqlCeReplication object. As we all know, if you modify the HostName value, you must then call the ReinitializeSubscription(true) method before calling Synchronize(). Unfortunately, this isn't working for me. Following this sequence of steps consistently results in error messages saying that I need to either call ReinitializeSubscription, or change my HostName back to it's original value. Has anyone ever experienced this problem?

Regards,

Rob Tiffany

Hey Rob,

You are correct, once you specify a HostName value for a subscriber, if you want to change that value later, you must call SqlCeReplication.ReinitializeSubscription(True) followed by .Synchronize() to effectively make the change.

I built an app recently for a large field service scenario where the mobile device can change hands from field worker to field worker. I decided to use the field worker's unique identifier as the value for HostName so that I could replicate only the appropriate data for a given worker to a given SQL Mobile database. If the device changes hands, here is the process of making the change to HostName and reinitializing:

Public Function Synchronize() As Boolean

Dim retVal As Boolean = False

Dim repl As SqlCeReplication = GetReplication()

If repl Is Nothing Then
Return False
End If

Try

If Globals.GetInstance().HostNameChanged Then
repl.ReinitializeSubscription(True)
Globals.GetInstance().HostNameChanged = False
End If

repl.Synchronize()

etc etc etc

If you are doing something similar and getting an error, could you post the details on the error itself and Laxmi and I can look into it? Also, some info on your configuration (assume you are talking about SQL Mobile and SS2005?)

Regards,

Darren

|||

Yeah, that's what I'm doing too. My Sync code blocks looks like the following:

private void replicate(bool reInit)

{

SqlCeReplication repl = new SqlCeReplication();

repl.InternetUrl = Settings1.Default.InternetURL;

repl.InternetLogin = Settings1.Default.InternetLogin;

repl.InternetPassword = Settings1.Default.InternetPassword;

repl.Publisher = Settings1.Default.Publisher;

repl.PublisherDatabase = Settings1.Default.PublisherDatabase;

repl.Publication = Settings1.Default.Publication;

repl.PublisherSecurityMode = SecurityType.NTAuthentication;

repl.SubscriberConnectionString = @."Data Source=" + localFolder + "MyDB.sdf;Password=p@.ssw0rd;";

repl.Subscriber = Settings1.Default.Subscriber;

repl.HostName = txtAgentId.Text;

repl.CompressionLevel = Settings1.Default.CompressionLevel;

repl.ExchangeType = ExchangeType.BiDirectional;

try

{

if (!File.Exists(localFolder + "MyDB.sdf"))

{

repl.AddSubscription(AddOption.CreateDatabase);

}

if (reInit == true)

{

repl.ReinitializeSubscription(true);

}

// Synchronize with SQL Server 2005

repl.Synchronize();

}

finally

{

repl.Dispose();

}

}

If the user changes the HostName, then I pass "true" to the method above to call ReinitializeSubscription(true).

Thanks,

Rob

|||

there is a possible code path in your code where the database does not exist and you (are going to create it using AddOption.CreateDatabase on the first replication), but reinit can also be true - this won't work - you cannot mix CreateDatabase and Reinit in the same call to Synchronize.

Could that be what's happening? What error messages are you getting?

Darren

|||

I don't have the exact error message in front of me now, but is says something like:

Error: Changing the HostName requires a call to ReinitializeSubscription() or change your HostName back to the original value. HostName = 2, Original HostName = 1.

Something like that. Keep in mind that it will even throw this same error when I call ReinitializeSubscription().

To answer your other question, I haven't experienced the possible code path error you described where the database doesn't exist. It should also be known that this is a desktop XP project and not on Windows Mobile. Just in case we have subtle differences or issues between the desktop and handheld dlls. I'm trying to do some load testing and the desktop is the only way to create enough clients.

Rob