How to connect to SQL Express 2005/2008/2008 R2 using SA account?

By default Microsoft will disable SA account and only Windows Authentication Mode is enabled. So when you try to connect to SQL Express instance using sa account it will not work. Also by default SA account is disabled in SQL Express instance

There are 2 things which we need to do to gain access using SA account

1. Enable SA account
2. Change authentication mode from Windows only to Mixed mode

And there are 2 ways to achieve this

1. GUI
1. Enable SA account

                                                               i.      Download SSMS Express for SQL Server Express (http://www.microsoft.com/downloads/)

                                                             ii.      Connect to SQL Express instance MACHINE\INSTANCE

                                                            iii.      Expand Security tab

                                                            iv.      Double click on SA account

                                                              v.      Under General page type in new password

                                                            vi.      Confirm the password

                                                           vii.      On Status page Enable the account

                                                         viii.      Click OK

2. Change authentication mode

                                                               i.      Right click on instance name in SSMS

                                                             ii.      Select Security page

                                                            iii.      Under Server authentication select “SQL Server and Windows Authentication mode”

                                                            iv.      Click OK

                                                              v.      Go to services.msc and restart SQL Express service

3. Connect using SA by SQLCMD or OSAL

                                                               i.      SQLCMD -S MACHINE\INSTANCE -U sa -P password

                                                             ii.      Osql –S MACHINE\INSTANCE –U sa –P password

2. Programmatically
1. Connect to SQL Express instance using NT authentication

                                                          i.      On command prompt use SQLCMD -S MACHINE\INSTANCE –E

2. Enable SA account

                                                          i.      Run following to change password and enable SA account
USE [master]

GO

ALTER LOGIN [sa] WITH PASSWORD=N'P@s$w0rd'

GO

ALTER LOGIN [sa] ENABLE

GO

3. Change authentication mode to mixed

                                                          i.      Using SQLCMD

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

4. Restart services for SQL express instance
5. Connect to SQL express using SA

                                                               i.      SQLCMD -S MACHINE\INSTANCE -U sa -P password

                                                             ii.      Osql –S MACHINE\INSTANCE –U sa –P password

As a security practice Microsoft disabled SA and hope you find this article helpful in enabling and using it.

How to change SQL server service account and password

Create a .BAT file using following script

MachineName is your server name
domain\account is account you want to set for SQL server service account
Pwd1234 is new password

Step 1 is to change credentials
Step 2 is to stop the service since changes do not take effect unless service is restarted
Step 3 is to start the service

Put this code in a batch file and run it from command prompt.

echo "changing pwd"

sc \\MachineName config "MSSQLSERVER" obj= domain\account password= Pwd1234 > ChangePwd.txt
echo "pwd changed"
pause
echo "stopping svr"
sc \\MachineName stop "MSSQLSERVER"  > SvrStop.txt
echo "svr stopped please verify"
pause
echo "starting svr"
sc \\MachineName start "MSSQLSERVER"  > SvrStart.txt
echo "svr started please verify"
pause

All output will be logged in 3 log files namely
ChangePwd.txt
SvrStop.txt
SvrStart.txt

You need to have admin rights on the machine

For details on SC (service controller) utility please check following link

http://technet.microsoft.com/en-us/library/bb490995.aspx

SQL Server 2008 SP3 database log file keeps growing and will not shrink

This last week I applied SQL Server 2008 Service Pack 3 (http://support.microsoft.com/kb/2546951) to our OLTP systems. However after a day or so I noticed that log files for one of the database was growing fast and even though transaction log backups were done periodically the size was not reducing. This server was publisher for transaction replication and multiple DBs were replicated to a subscriber. Also server was clustered with one active and one passive node.

When I checked the open transactions by running following on affected database

dbcc opentran

Here is the output of the command

Oldest active transaction:

    SPID (server process ID): 7s

    UID (user ID) : -1

    Name          : tran_sp_MScreate_peer_tables

    LSN           : (94443:31848:1)

    Start time    : May 09 2012  1:01:08:544AM

    SID           : 0x01

Replicated Transaction Information:

        Oldest distributed LSN     : (94753:23165:12)

        Oldest non-distributed LSN : (0:0:0)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now what is this script doing 3 days after the SP3 installation

First see the recovery model of the database in question

select name, recovery_model_desc, log_reuse_wait_desc  from sys.databases where name in ('testdb')

Then check open transactions. You should see the same transaction (tran_sp_MScreate_peer_tables) in results.

select * from sys.dm_tran_active_transactions

What happens is while applying SP3 some upgrade scripts are run for Peer to Peer replication setup (Even though you are not using it will still run)

Resolution:

First check following registry setting on both nodes or single if it is stand alone instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.InstanceName\Replication\Setup

If value is 0 that indicates that replication upgrade was not completed successfully during SP3 install.

So here is what you should do

1.      Stop all applications from connecting. Yes this is required else deadlock will happen

2.      Run msdb..sp_vupgrade_replication on the server. This will upgrade the replication setup and will run peer table setup script again (Which was the open transaction)

3.      Restart SQL Service.

4.      After restart for some time server will not allow connections and applications will see following error if connecting to the server 'Server is in script upgrade mode. Only administrator can connect at this time. '

5.      After restart wait for few minutes and then check the registry value again. It should change to 1

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.InstanceName\Replication\Setup

After doing this log file size dropped and there were no open transactions

What’s IPv4 and why do you need to move on to IPv6?

Today is the official launch of Internet Protocol Version 6. So on this occasion I am writing about few things which all IT folks should know about Internet Protocol and different versions of it.

Internet Protocol or IP addresses are used to identify a device which is connected to the Internet. All devices on the Internet are addressed using a protocol set by Internet Assigned Numbers Authority (IANA http://www.iana.org/). This organization is responsible for all IP address pools. IANA distributes large blocks of IP addresses to Regional Internet Registry (RIR http://en.wikipedia.org/wiki/Regional_Internet_registry) which intern distributes them to vendors. Most of the Internet is currently addressed with Internet Protocol Version 4 (IPv4).

IPv4 format has four numbers with three digits each which are separated by dot. For example: 192.168.10.1

This allows IPv4 to have 4,294,967,296 individual IP addresses. According to IANA we ran out of IP addresses using IPv4 in Feb 2011. What this means is you can not get on to the Internet using a new device using IPv4. However individual RIR still have some pools of IPs which they are distributing. Asian RIR has already run out of IP addresses under IPv4.

So IANA has come up with a new version of called IPv6 which is successor of IPv4. Both versions will be used for many years since it is realistically impossible to replace all devices which have IPv4 address. However moving on most of the devices will be using IPv6.

IPv6 is works same as IPv4 by providing unique number for each device to communicate over the Internet. IPv6 uses 128 bit addresses as opposite to IPv4 which uses 32-bit addresses. So IPv6 has significantly large number of unique combinations. IPv6 IP address has eight groups with each group having 4 alphanumeric values separated by colons. For example:

2012:ABC1:123E:6786:ADDC:1234:ABCD:88YY

Maximum number of IPs which IPv6 can hold is 340,282,366,920,938,463,463,374,607,431,768,211,456 which I think is 340 trillion trillion trillion.

IPv5 also exists as Internet Streaming Protocol used for streaming audio, videos over the Internet. It was never widely distributed and now is called ST2.

What if your organization does not move to IPv6?

It means you are stuck with whatever IPs you have with IPv4. You will have to reuse those IPs as you grow. Also as world moves on to IPv6 they wont be able to connect directly to your website or application if you are still using IPv4.