How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Manage

I installed Microsoft SQL Server 2005 Express Manager and connect to SQL 2000 normally
but when I connect to Microsoft SQL Server 2005 CTP, I failed! when I open SQL Configuration Manager,
I find the SQL Server had been running, after I opened "Attribute" window, I find "This account" box had been checked,
and "NT AUTHORITY\NETWORK SERVICE" was displayed in Account Name edit box, "**********" was displayed in "Password" edit box.

I think that the reason I can't connect to Microsoft SQL Server 2005 CTP is that I can't know the name of SQL Server 200, account, password.
right? In Microsoft SQL Server 2000 I can find the name of server from "Service Manager" and default accunt is "SA", the default password is blank.
but I can't find it in Microsoft SQL Server 2005. Any more, when I open "Express Manager", there are two radio button, "Windows Authentication" and "SQL Server Authentication",
which one will I choose? Does "Server Instance" point the name of SQL Server 2000? or Does "Server Instance" point the name of my computer?

Hi,

Try connecting with '.\SqlExpress' as the instance name. If you haven't specified a username and password then, windows authentication is the mode of logging into Sql2005...

cheers,

Paul June A. Domag

|||Hi,

You can also enable SQL Authentication. Follow instructions to enable it:
Enabling SQL Authentication

STEP1:
You need to enable SQL Authentication on the machine. This is complex in SQLExpress and you need to edit the registry. Copy the following lines of text onto a Notepad and save it with extention .REG.
-COPY BELOW
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000002
-COPY TILL THIS
STEP2: Double Click on the .REG file to change the registry. This change the Authentication Mode to enable SQL Authentication.

STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

Next, you need to add a user with SQLLogin.
To do this: Login to SQLExpress using Express Manager and run the script below:
-
USE MASTER
sp_addLogin 'newuser', 'newuser@.123'
GO
sp_addsrvrolemember 'newuser', 'sysadmin'
GO
-
Once this is done, you can logon with the username/password combination.

Regards,
Vikram

|||Hi Vikram,
thank you for this post - was of great help !

Regards,
Fabian
|||Hey this is very cool! But I have a question- in the past it seems these settings follows each individual SQL Server Instance, but now it seems its a global setting as it does not require any instance specific configuration?|||

Help!!!

Please help me sort through this mass of web server information and configuration. I don't know where to begin?

l

|||

Thank you, I am a novice in this area.

Charles

|||

Hi William,

No, configuration is still instance specific. Each instance has a seperate registry hive in the format:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x
where x is some integer.

The mapping from MSSQL.x to the actual instance name can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names.

Il-Sung.

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.

How about getting a dedicated server to run and manage SQL jobs

Hi all:

Give me some feedback.

In my company, we have 10+ sql servers and many sql jobs running on every SQl server.

What about the idea that running all jobs on a dedicated sql servers?

at least 2 advantages

1.easy to managment

2.if agent server down, we restart the server not to impact the downtime of the production server.

any suggestion is welcome.

We had a similar setup which worked well.
Basically had big machines for SQL server and then used blades for "distributed" SSIS jobs.
All you really need is fast procs, lots of memory and not much disk space.
The idea was that as one box would become loaded, we would introduce another. All boxes would have the same jobs on it, but some disabled. The packages were run from a network location so should a box die, we can switch over quite quickly.

The issues are:
Licensing
Network performance
Cost

But if those are not a problem, it is a nice solution.