Mostly SQL Things

We need to change the port sql server uses from the default to… something else. Towards that end I want to make aliases on all the end user computers and other sql servers so they can still connect without problems.

Thank you to microsoft’s scripting guy and this article.

First I made a file with all the computers in the domain. Save this as getadcomputers.ps1 and then run it within powershell like this: .\getadcomputers.ps1 > .\computer.txt

# run as the following to save all the AD registered computers
# .\getadcomputers.ps1 > .\computer.txt

$strCategory = “computer”

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = (“(objectCategory=$strCategory)”)

$colProplist = “name”
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
{$objComputer = $objResult.Properties; $objComputer.name}

Then I added that to a friendly Access table, decided what computers to exclude, and made a routine to loop through and create the aliases.

Sub AddRegKey()

‘ Loops through the table sfhpComputers and
‘ 1. checks if the keypath & value exists
‘ 2. if yes then logs a message and continues
‘ 3. if not then adds the key and checks the add worked and logs a message

Dim strKeyRoot, strKeyPath, strValueName, strData, strValue, strType
Const HKEY_LOCAL_MACHINE = &H80000002
strKeyRoot = “HKEY_LOCAL_MACHINE”
strKeyPath = “SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo”
strValueName = “newAliasName”
strData = “DBMSSOCN,fullSQLServerName,NewPortNumber”
strType = “REG_SZ”
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(“select name, Note1, Note2 from Computers”)

With rs
Do While Not .BOF And Not .EOF
strComputer = !Name

‘ Check if the key exists
Set objregistry = GetObject(“winmgmts:\\” & _
strComputer & “\root\default:StdRegProv”)
objregistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue

If Not IsNull(strValue) Then
.Edit
!Note1 = “The registry key ” & strValueName & ” – ” & strData & ” already exists.”
!Note2 = Null
.Update
GoTo nextComputer
End If
‘ End check if the key exists

‘ Begin code to add registry key
shellcmd = “reg.exe add \\” & strComputer & “\” & strKeyRoot & “\” & strKeyPath & ” /v ” & strValueName & _
” /t ” & strType & ” /d ” & strData

output = Shell(shellcmd)
‘ End code to add registry key

‘ Now check the key was added
Set objregistry = GetObject(“winmgmts:\\” & _
strComputer & “\root\default:StdRegProv”)

.Edit

If Err = 462 Then
!Note1 = “Computer ” & strComputer & ” does not exist.”
!Note2 = output
GoTo nextComputer
End If

objregistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue

If IsNull(strValue) Then
!Note1 = “ERROR: The registry key ” & strValueName & ” – ” & strData & ” not added successfully.”
!Note2 = output
Else
!Note1 = “The registry key ” & strValueName & ” – ” & strValue & ” added successfully.”
!Note2 = output
End If

.Update

nextComputer:
.MoveNext
Loop
End With
End Sub

What to do if database mail is not working.

Mail was not being sent. First I queried

SELECT * FROM msdb.dbo.sysmail_event_log

and saw in the description field

“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-03-31T12:24:10). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [ip address]:[port number]). )”

Running

EXEC msdb.dbo.sysmail_help_status_sp ;

returned “inactive”

Some websites suggested that the people trying to send mail need to be in the msdb role DatabaseMailUserRole, so I checked that

EXEC msdb.sys.sp_helprolemember ‘DatabaseMailUserRole’ ;

but then I remembered I am a sysadmin and am not able to send mail.

Then I practically ran this

select name , is_broker_enabled from sys.databases

and this and then could send mail:

alter database msdb set single_user with rollback immediate
alter database msdb set enable_broker
alter database msdb set MULTI_USER

These procedures may be helpful too:

exec msdb..sysmail_help_principalprofile_sp
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = ‘mail’ ;
exec msdb..sysmail_stop_sp
exec msdb..sysmail_start_sp

Except the jobs still could not send mail. For some reason sql server agent did not have database mail set up. After pointing and clicking to get that together I thought I was done, except now I got this error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-07-06T14:10:00). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.). )

This error was because the operators were defined with mapi names and not full email addresses.

Whew done!