How can I connect to a remote sql server using windows authentication?

It is simple

1- I open the Sql Server 2005 Management Studio

2- I select Windows Authentication from the drop down.

3- I cannot write the user name and password, it chooses the default once, the one I am logged in with!

But I am in a virtual machine outside the domain controller, I can access shares on machines that are on the domain controller, thanks to the file sharing of windows, but I cannot login to sql server, thanks to a meaningless restriction on that dialog :-)

Now, how can I still use the Windows Authentication and login, how can I avoid the sql server authentication?

If you are outside domain, then you should use SQL Authentication.|||

Ok, then why the text boxes for the user name and password are still there if I choose the Windows authentication, and the text boxes have the user name already filled in, and all are disabled.

What is the point of having those there? In my case, I was trying to find a way to enable them from the settings; I guess just a false hope.

And why cannot I use the windows authentication? NTFS does allow me to do it and access the file system from outside the domain using windows authentication against the domain, what does make sql server more special?

|||

The reason for the textbox is just to let you know which Windows account is being used to connect to SQL Server using Windows authentication. To access SQL Server using SQL authentication, click the Authentication drop-down to see the SQL Server Authentication option. You'll see the User name and Password textboxes enabled.

If you want to use Windows authentication, the easiest way is to join your SQL Server to your domain.

|||

Thank you for the help, but I know how to use the SQL Server authentication, and the SQL Server is the development server and it is on the domain.

My virtual machine is the development machine, it is a virtual machine and it cannot join the domain, it must stay as it is, the real machine is on the domain, but the virtual machine that I am trying to use is not.

From the virtual machine I can do lots of things, including accessing the file system and the intranet sites on the domain, using the domain authentication box, or cached credentials, but I cannot do that with the SQL Server.

|||

if you want an nt authentication

then you must promote your virtual machine to a domain controller

how best to tackle logins with SQL authentication after automatic failover

In an sql authentication environment with an automatic failover in database mirroring how to you manage new logins which have been created on the principle since the start of mirroring? Since the master cannot be mirrored, and the mirror database cannot be read during mirroring (except as a snapshot) in order to find the missing logins, I assume that only after failover a script should run to create the new logins and then run sp_change_users_login . The qestions are:

1) should the script create a new login first and then run sp_change_users_login with option update_one , or should sp_change_users_login using option

Auto_Fix create the missing logins?

2) But what is the password of these users? is it initially NULL , as a consequence of sp_change_users_login? What about the SIDs?

3) Or should we bypass sp_change_users_login altogether and use

CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...as described in http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx

4) What is the event that would trigger this script to run after the aitomatic failover ?

Is there a definitive MIcrosoft agreed apon and recommended method to tackle this?

If you want your application users to connect after failover you better create those logins beforehand @. both the principal and @. mirror server @. the time of configuring mirroring so that they will be available to you but will be mismatched and after failover you can correct them using sp_change_users_login sp.

and for the less important ones you can make use of SSIS to transfer logins frequently so that after failover you can just restore the master syslogins table or master db and fix the mismatch by change users login sp......

|||

Thank you for your anwer. My question was about the new logins created after mirroring had begun. Of course before mirroring started the existing logins were recreated on the mirrorserver beforehand . The question was whether sp_change_users_login should be used with update_one or Auto_Fix or not used at all . The auto fix option creates a login if there isn't one. The update_one option does not .

Assuming rhat SSIS would transfer the new logins from the princiopal master to the mirror master while database mirroring would transfer the users from the principal to the mirror. Will the SIDs be in order? If a script has to be run it would have to be run automatically so what is the trigger that this script will be triggered by?

|||

Actually there are a few issues here:

Firstly SSIS will not help you because as of 2005 the Transfer Logins task does not transfer the passwords, it resets the passwords which to be honest wouldn't help especially if you are using the High Availability option.

Next, you need to take care of the SID's otherwise you will have to sync them. See this KB article

Lastly, In 2005 you cannot create a login which has a default database that is offline. So if an app needs to use Automatic Client Redirect in a High Availability setup and the account is created on the Principal after mirroring has been configured, you will not be able to add the login on the Mirror. The only workaround is to bring the mirror online periodically and sync the logins. I heard it may be fixed in 2008 however; I have not tried yet, maybe someone can clarify for me.