You know, more than just about any other question I get the most common is around connection security for SQL Server Management Studio. Let’s take a couple of scenarios and walk through the basics.
Windows Accounts on SQL Server (Windows Authentication)
This setting means that you can use your current Windows account and log in to SQL Server. This is the first cause of confusion. Your current account is whatever you started Management Studio with. If you’re trying to connect to a server that is on a different domain (that isn’t trusted) than your account or if it is in a Workgroup, in XP just right-click the Management Studio and select “Run As”. Then enter what you need to connect to that box.
In any other tool, just supply the correct connection string and you’re in.
SQL Server Accounts (SQL Server Authentication)
In this mode, you just supply your SQL Server name and password that you create and control within SQL Server. Yes, it’s very secure, and if your SQL Server is running on Windows 2003, you can even use some of the policies such as password lengths and lockouts in SQL Server. You don’t have to be on the same domain, workgroup – you can even connect from UNIX boxes or other OS’s.