Enable Mixed Mode Authentication After Installation of MSDE

This step-by-step article describes how to establish a connection to Microsoft Desktop Engine (MSDE).

MSDE uses two authentication modes:

Windows Authentication Mode (Windows Authentication)
In Windows Authentication Mode, a user can connect through a Microsoft Windows NT 4.0, a Microsoft Windows 2000, or a Microsoft Windows XP user account.

Mixed Mode (Windows Authentication and SQL Server Authentication)

In Mixed Mode, users can use either Windows Authentication or SQL Server Authentication to connect to an instance of MSDE. Users who connect through a Windows NT 4.0, a Windows 2000, or a Windows XP user account can use trusted connections in either Windows Authentication Mode or Mixed Mode.

The default authentication mode for MSDE is Windows Authentication. MSDE installs with a built-in system administrator (SA) user account. However, because SQL Server Authentication is disabled by default, you cannot access the built-in account after a typical installation.

By default, the value of the LoginMode Windows registry subkey is set to 1 for Windows authentication. To enable Mixed Mode authentication after installation, you must change this value to 2.

Resolution :

The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance. If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

To change the value of LoginMode to 2, follow these steps:
In Control Panel, open the Services tool to stop MSSQLSERVER and all other related services (such as SQLSERVERAgent)
To open Registry Editor, click Start, click Run, type regedit, and then click OK.

Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\
-or-
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\
In the right pane, double-click the LoginMode subkey.

In the DWORD Editor dialog box, set the value of this subkey to 2, make sure that the Hex option is selected, and then click OK.

Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.

How to Change Server Authentication Mode

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.


On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

To restart SQL Server from SQL Server Management Studio
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
To enable the sa login by using Management Studio
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, you might have to create and confirm a password for the sa login.
On the Status page, in the Login section, click Enabled, and then click OK.

To enable the sa login by using Transact-SQL
Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '' ;
GO

Last execution time of a stored Procedure

Select DB_Name(dest.[dbid]) As 'databaseName'
    , Object_Name(dest.objectid, dest.[dbid]) As 'procName'
    , Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest
Where dest.[text] Like '%SP_FAST_Style_Colors%' -- replace
    And dest.[dbid] Is Not Null  -- exclude ad-hocs
Group By db_name(dest.[dbid])
    , Object_Name(dest.objectid, dest.[dbid])
Order By databaseName
    , procName
Option (MaxDop 1);