Here is some code for generating a nice 128 character SA password; have fun:

set nocount on

declare @counter int,

@password varchar(128),

@char char(1),

@charindex int,

@loop int

select @counter = 1, @password = ''

while @counter < 2

--Restrict the password to 0-9, A-Z, and a-z

select @loop = 1

while @loop = 1

begin

select @charindex = convert(int, rand() * 254)

if (@charindex between 65 and 90 or @charindex between 97 and 122)

and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

--or @charindex between 161 and 255 or @charindex between 130 AND 140

select @loop = 0

end

--Accumulate characters for password string

select @char = char(@charindex)

select @password = @password + @char

select @counter = @counter + 1

while @counter < 4

--Restrict the password to 0-9, A-Z, and a-z

select @loop = 1

while @loop = 1

begin

select @charindex = convert(int, rand() * 254)

if (@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122)

and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

--or @charindex between 161 and 255 or @charindex between 130 AND 140

select @loop = 0

end

--Accumulate characters for password string

select @char = char(@charindex)

select @password = @password + @char

select @counter = @counter + 1

while @counter < 5

--Restrict the password to 0-9

select @loop = 1

while @loop = 1

begin

select @charindex = convert(int, rand() * 254)

if @charindex between 48 and 57 --or @charindex between 65 and 90 or @charindex between 97 and 122

and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

--or @charindex between 161 and 255 or @charindex between 130 AND 140

select @loop = 0

end

--Accumulate characters for password string

select @char = char(@charindex)

select @password = @password + @char

select @counter = @counter + 1

while @counter < 10

-- Restrict the password to NOT 0-9, A-Z, and a-z

select @loop = 1

while @loop = 1

begin

select @charindex = convert(int, rand() * 254)

if --@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122

--or

(@charindex between 161 and 255 or @charindex between 130 AND 140)

and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

select @loop = 0

end

--Accumulate characters for password string

select @char = char(@charindex)

select @password = @password + @char

select @counter = @counter + 1

while @counter < 11

--Restrict the password to 0-9

select @loop = 1

while @loop = 1

begin

select @charindex = convert(int, rand() * 254)

if @charindex between 48 and 57 --or @charindex between 65 and 90 or @charindex between 97 and 122

and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

--or @charindex between 161 and 255 or @charindex between 130 AND 140

select @loop = 0

end

--Accumulate characters for password string

select @char = char(@charindex)

select @password = @password + @char

select @counter = @counter + 1

while @counter < 129 -- use this for regular passwords

--Restrict the password to 0-9, A-Z, and a-z

select @loop = 1

while @loop = 1

begin

select @charindex = convert(int, rand() * 254)

if (@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122 or @charindex between 161 and 255 or @charindex between 130 AND 140)

and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

select @loop = 0

end

--Accumulate characters for password string

select @char = char(@charindex)

select @password = @password + @char

select @counter = @counter + 1

select RTRIM(@password) AS Password

Nice article.  We only use SA for database ownership and sometimes for SQL Job

ownership.  It is never used for connecting to the server because no one knows what

the password is.  We use a simpler routine to generate a random 72 character value

for it.

DECLARE @pwd char(72)

SELECT @pwd=convert(char(36),newid())+convert(char(36),newid())

EXECUTE master..sp_password null,@pwd,'sa'

Sample value:

0A8A24E8-A728-4DCF-B561-179511138895AAB9C183-BC26-49B3-BDC5-009AFFA5B83B

You are right about SQL ids not going away. Many developed and purchased applications

 use them for simplicity and to reuse execution plan

"Only query plans with the same user ID are candidates for reuse." 
See http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

There are times when we DBA's need to use a SQL id. 

1. Domain is having issues.
2. IT is changing domains.
3. Remote access to an instance located at another company

To accomplish this we still don't use SA, instead we have a SQL Id with system

administration authority and assign it a complex password.

Thanks for giving me the opportunity to spout off about not using SA and having to

use mix mode

  The problem is that most basic bundled software packages REQUIRE 'sa' to be used to do upgrades that I know of. It is very sad that software companies utilize 'sa' because it is just easier to use the 'god' right ID. Once I attempted to create an ID, assign it 'sa' rights for a temporary ID for one of these software apps we have to do an install/upgrade. It never worked correctly. I could not figure out why, so I changed the 'sa' password, we did the upgrade and I changed it back. Odd, very odd.

  Some of the software vendors that come on-site here to install a new SQL Server based app want 'sa' password to be a word or sa... I over ride that and they want the user account password to be the same as the login or a simple word.... I override that idea too. A couple have come on-site to install and with that thought process and are not happy that I require a more difficult password as the application is already setup for a specific ID and password.... jeesh....  

Thanks. Always good to read articles about security.

In our case we have a strong password for the sa account. To keep track of all the DBA activities we use for several DBA's a personal administrator account.

Only Windows authentication is not an option for us. The database administrators rights are of less concern than all the users being capable of login in with or without Windows authentication with a lot of tools other than the application where the database in the first place is used for.

We have build our own software with a built-in password scrambler. So the password you use to connect to the database is different from the one you type in at connect time. That's our solution to keep all those end users out of the database. So in no way Windows authentication for us!