July 2008 - Posts

More useful DMV queries

in the attached file you can find queries about Blocking, DiskIO, Memory, Running Queries, Indexes etc ...

Itay Braun                        Premier Field Engineer - SQL Server and BI                        Microsoft Services - UKE-Mail: itayb@microsoft.com       Mobile: +44-796-928-9996         Blog: http://sqlblogcasts.com/blogs/thepremiers/

Veni              Vidi              Fixit

Microsoft Security Bulletin MS08-040 - Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (Important)

Executive Summary

This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.

This security update is rated Important for supported releases of SQL Server 7.0, SQL Server 2000, SQL Server 2005, Microsoft Data Engine (MSDE) 1.0, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2000 Desktop Engine (WMSDE), and Windows Internal Database (WYukon). For more information, see the subsection, Affected and Non-Affected Software, in this section.

The security update addresses the vulnerabilities by modifying the way that SQL Server manages page reuse, allocating more memory for the convert function, validating on-disk files before loading them, and validating insert statements. For more information about the vulnerabilities, see the Frequently Asked Questions (FAQ) subsection for the specific vulnerability entry under the next section, Vulnerability Information.

Recommendation. Microsoft recommends that customers apply the update at the earliest opportunity.

http://www.microsoft.com/technet/security/Bulletin/MS08-040.mspx 

Virtual Earth -> Sql Server 2008 Geospatial Data Generator

 

Virtual Earth -> Sql Server 2008 Geospatial Data Generator

 

Check this out!

The Virtual Earth -> Sql Server 2008 Geospatial Data Generator (http://mikeo.co.uk/demo/sqlspatial/default.aspx ) is a great tool which generates SQL Server 2008 Geospatial data.

Draw the polygon on the Virtual Earth Map and the T-SQL which inserts the geospatial data is generated automatically.

 

Cool.

 

Special Thanks to Keith Burns, a Data Architect in Microsoft, who told me about this website.

 

Cheers,

 

Itay Braun                        Premier Field Engineer - SQL Server and BI                        Microsoft Services - UK

E-Mail: itayb@microsoft.com       Mobile: +44-796-928-9996         Blog: http://sqlblogcasts.com/blogs/thepremiers/

Veni              Vidi              Fixit

 

 

SQL Server 2005 (64-Bit) Multi-Instance Installations
When you start Installing a SQL Server 2005 (64-Bit) Multi-Instance Farm environment, you may encounter this particular error message during the Installation process itself.  Your first SQL Server 2005 ‘Default’ Instance would be successfully installed and then patched upto Service Pack 2.  However, when your second ‘Named’ Instance was started – this specific error message might be displayed during the SQL Server 2005 Database Services Installation process : [Microsoft][SQL Native Client][SQL Server]Password validation failed.  The Password does not meet Windows Policy requirements because it is too short.  To continue, correct the problem, and then run SQL Server Setup again.  Launch script file C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Install\instmsdb.sql

The reason that this error message occurs is because your Windows Server 2003 GPO (Group Policy) may be stipulating a password which is greater than 8 characters and this particular password "Yukon90_" (located in the instmsdb.sql file) is only 8 characters in size :

create certificate [##MS_AgentSigningCertificate##]    encryption by password = 'Yukon90_'   with subject = 'MS_AgentSigningCertificate'

A workaround solution has been discovered, in relation to this Microsoft KB (Knowledge Base) Article : Error message in the SQL Server Errorlog file after you upgrade SQL Server 2000 to SQL Server 2005 on a server that is running Windows Server 2003: "Unable to update password policy" – http://support.microsoft.com/kb/936892/en-us.Which provides us with the information that this Trace Flag (-T4606) can be used to bypass the Windows Server 2003 Local GPO Password Policy requirements for a SQL Server 2005 Installation.  This Trace Flag is added into the Windows Server 2003 System Registry before any subsequent Instances of SQL Server 2005 (whether that be 2, 3, 4, 5, 6 etc) commence, it’s entered into this Registry Key Hive : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters

Where the ‘x’ represents the actual numerical increment to the SQL Server 2005 Multi-Instance Environment.

Using BDF_Set_Key.exe

In order for this Registry Key entry to become consistent with any subsequent SQL Server 2005 Instances, a specific executable has been created which will provide us with some continuity here.  This BDF_Set_Key.exe will need to be placed into the <Root_Installation_Path>\SQL Setup folder and executed (double-clicked) from this location before the next Installation (2nd, 3rd or 4th etc) of SQL Server 2005 – which will in essence add this Trace Flag (-T4606) into the System Registry to the appropriate position.  For example:
  1. During the First SQL Server 2005 (64-Bit) Installation Process as a ‘Default Instance’ –
  2. Database Services would be Installed into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN folder
  3. Analysis Services would be Installed into the C:\Program Files\Microsoft SQL Server \MSSQL.2\OLAP\BIN folder
  4. Reporting Services would be Installed into the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\Report Server\BIN folder
  5. Second SQL Server 2005 Installation as a ‘Named Instance’ –
  6. Database Services would then be Installed into this C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\BINN folder
Therefore this Trace Flag (-T4606) would then be added to this System Registry Hive : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer\Parameters

When you execute this BDF_Set_Key.exe, a Windows NT Command Window will ‘pop-up' - along with this Dialogue Box :

You can verify that this Trace Flag has been successfully added into the Windows Server 2003 System Registry by checking the output “BDF_Set_Key.log” file, which will be positioned in the same folder structure (<Root_Installation_Path>\SQL Setup) from where this BDF_Set_Key.exe file was launched.

Using BDF_Del_Key.exe

Once your next SQL Server 2005 Installation has completed successfully and received the Service Pack 2, this System Registry Key can then be removed from the Hive using this ‘BDF_Del_Key.exe’ executable file.  The file should also be in this position <Root_Installation_Path>\SQL Setup folder and executed (double-clicked) from there.  When this file is run, as before a Windows NT Command Window will ‘pop-up' along with this dialogue box :

You can verify that this Trace Flag has been successfully removed from the Windows Server 2003 System Registry by checking the output “BDF_Del_Key.log” file, which will be positioned in the same folder structure (<Root_Installation_Path>\SQL Setup) from where this BDF_Del_Key.exe file was launched.

These BDF_Set_Key.exe and BDF_Del_Key.exe files have been attached to this Web posting as "BDF_Keys.zip".  There is also an MSDN forum posting here : Re: SP1 install problem with password - http://forums.microsoft.com/msdn/showpost.aspx?postid=2238519&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

Thank you,
Gareth Ford
Premier Field Engineer - Microsoft | Services | Mob:+44(0)7791 191780 | 0870 501 0800 (Technical support UK only)  http://www.microsoft.com/uk/support http://www.microsoft.co.uk/premierpeople/ Microsoft Limited (company number 01624297) is a company registered in England and Wales whose registered office is at Microsoft Campus, Thames Valley Park, Reading. RG6 1WG

Blog: http://sqlblogcasts.com/blogs/thepremiers/ People Talk, I deliver

Search

Go

This Blog

Tags

Syndication