Configuring Certificate for MSX (Master/Target Server Environment)

The certificates allow the SQL Servers to utilize SSL (required for the master target environment) and also a much more secure way of protecting our SQL login information which is transmitted in clear text across the network from our internet facing servers. Enabling SSL allows us to better protect these logins because they would be encrypted."
1) Install Certificate. - Import .pfx file provided by operations onto the sql server.
#1.1 - double click pfx file to begin certificate import. wizard will confirm file name. click 'next' to confirm
#1.2 - provide private key password, click next
#1.3 - select 'place certificates in following store' , select 'personal' , OK
#1.4 - click next on confirmation page, 'hopefully recieve the message - 'the import was successful'

2) Associate the certificate with sql instance
#2.1 - start > run > regedit {enter]
#2.2 - Use Regedit to navigate the registry to >
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\SQLServerAgent\

Set MsxEncryptChannelOptions(REG_DWORD) to 2


Useful Links

Troubleshooting MSX >
http://blogs.ameriteach.com/chris-randall/2007/8/23/sql-server-2005-troubleshooting-multi-server-administration-.html

setting encryption options on target servers >
http://msdn.microsoft.com/en-us/library/ms365379.aspx

configuring certificate for use by ssl (by mmc) >
http://support.microsoft.com/kb/316898

configuring certificate for use by ssl (commands) >
http://msdn.microsoft.com/en-us/library/ms186362.aspx

JDBC v1.2 Application connectivity issue

Debugging an application connectivity issue - No data returned.

Steps taken -

1. sql traces performed (stored procedure was being executed against db)
   
2. sp returns data (proved this by running the traced code manually in a query window)
   
3. became puzzled...
   

Solution - SQLServerException: The statement did not return a result set when sp uses cursors

The Java code was using executeQuery rather than execute . This meant that executeQuery was effectively seeing 'inside' the stored procedure and was looking at a recordset in use by a cursor, rather than the final recordset returned when the query was run manually.

"The method executeQuery is designed for statements that produce a single result set, such as SELECT statements"

"The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two."

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1710309&SiteID=1

JDBC connecting to a named instance of sql 2005

Connection string for connecting JDBC (Java database Connectivity) to a named instance of sql >

JdbcURL=jdbc:sqlserver://RIO:2433;instanceName=SQL2005;databaseName=Statistics


(i needed this when an application was provided to us and the ISV had not heard of a 'named instance' before :)

Common UK DateTime Formats

Some common DateTime conversions I use -

SELECT CONVERT(varchar(10), getdate() , 103) 

09/09/2007

SELECT CONVERT(varchar(10), getdate() , 120) 

2007-09-09

SELECT CONVERT(varchar(20), getdate() , 120) 

2007-09-09 12:18:29

strip non-numeric characters (e.g to use datetime in a filename) -

SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar(20), getdate() , 120),'-',''),' ',''),':','')

20070909123203