May, 2010

May, 2010

  • Care, Share and Grow!

    Export SQL table records to XML form

    • 1 Comments

    I am not a SQL guy. But while working on something I found this which seemed cool to me. So thought of sharing it with folks in case you are not aware. Using SQL Management studio and running T-SQL command we can export the Database table entries into an XML form.

    Let's say you run this SQL command in the SQL editor.

    SELECT * from Saurabh.dbo.Customers

    And we get the following output in the table.

    clip_image001

    Now in order to export the table content into an XML form we need to use FOR XML PATH as below:

    SELECT * from Saurabh.dbo.Customers

    FOR XML AUTO

    clip_image002

    Or

    SELECT * from Saurabh.dbo.Customers

    FOR XML RAW

    clip_image003

    Or

    SELECT * from Saurabh.dbo.Customers

    FOR XML PATH

    Or

    clip_image004

    …..

    Or

    SELECT * from Saurabh.dbo.Customers

    FOR XML PATH('Customer')

    clip_image005

    …….

    If you want to wrap the content under a specific ROOT node use the following:

    clip_image006

    ….

    ….

    clip_image007

    It may not be something new for the SQL folks but this was something new to me.

    Reference:

    http://msdn.microsoft.com/en-us/library/ms189885.aspx

    http://theengineroom.provoke.co.nz/archive/2007/04/27/using-for-xml-path-a-primer.aspx

    till next time….

  • Care, Share and Grow!

    SSL Bindings are randomly getting deleted for a website with error "SSL Certificate Settings deleted for Port : X.X.X.X:443" in the event logs (IIS 7.0/7.5)

    • 6 Comments

    Symptoms

    Are you getting into a scenario wherein randomly your Website loses or changes the SSL certificate bindings from within the IIS manager? And you realize it only when users complain that they are unable to reach the HTTPS site, or that they get a certificate warning. They are able to access the website over HTTP but not over HTTPS because the certificate binding is lost for the website, or they may be prompted that the certificate is expired, or the site name is incorrect. At this point of time you also notice that System Event log entry shows the following:

    Log Name:      System
    Source:        Microsoft-Windows-HttpEvent
    Date:          3/31/2010 2:43:28 PM
    Event ID:      15300
    Task Category: None
    Level:         Warning
    Keywords:      Classic
    User:          N/A
    Computer:      myMachine
    Description:
    SSL Certificate Settings deleted for Port : 0.0.0.0:443 .

    If you go to the IIS manager and check the bindings for the Website in question you will see either the certificate binding is lost or some other certificate is listed.

    You can also crosscheck the registry entry below for your IP/Port binding associated with your website and you may find it deleted.

    HKLM\System\CurrentControlSet\SERVICES\HTTP\Parameters\SslBindingInfo\X.X.X.X:443

    Here is how it looks if you have the proper binding set at the http.sys level in the Registry.

    clip_image002

    Note:  Should the certificate be changed, the binding will look the same.

    Assessment

    If you are experiencing the above problem it could be related to the following <customMetadata> tag in your ApplicationHost.config.

    <key path="LM/W3SVC/X">

    <property id="5506" dataType="Binary" userType="1" attributes="None" value="oXiHOzFAMOF0YxIuI7soWvDFEzg=" />

    </key>

    The above property is used to store a SSL certificate hash. It is specifically the ID 5506 entry you need to check for. That ID is the legacy property for certificate hash. Whenever any application/service which depends upon the ABO mapper runs/starts, it tries to initialize the ABO tree structure which includes generating custom nodes and properties. During this process it reads from this custom metadata section and tries to map the properties in the ABO tree structure. During mapping it deletes the current SSL mapping(s) at the http.sys level and recreates a new one using the above hash value. If this value is invalid for some reason it fails to add the new entry for SSL binding at the http.sys level. So in such a case the above registry key does not have an entry for the website’s IP:Port combination corresponding to the SSL setting in the UI like below:

    clip_image004

    Call stack output

    If you use the Debugging Tools for Windows and the Microsoft symbol server to attach to the process Inetinfo.exe, you will notice a call stack that resembles the following below:

    abocomp!UpdateSSLProperty
    abocomp!SITE_CUSTOM_PROVIDER::MapSetData+0x371
    abocomp!ABO_NODE::MapSetData+0xd9
    abocomp!ABO_NODE::SetData+0xbd
    abocomp!ABO_TREE::SetCustomProperty+0x34a
    abocomp!ABO_TREE::GenerateCustomNodesAndProperties+0x1ad
    abocomp!ABO_TREE::GenerateTree+0x28d
    abocomp!ABO_WRAPPER::InitializeTreeAndState+0xad
    abocomp!ABO_WRAPPER::GetCurrentAboTree+0xc7
    abocomp!ABO_WRAPPER::OpenKey+0x154
    COADMIN!CADMCOMW::OpenKeyHelper+0x172
    COADMIN!CADMCOMW::OpenKey+0x53
    RPCRT4!Invoke+0x65
    RPCRT4!NdrStubCall2+0x348
    RPCRT4!CStdStubBuffer_Invoke+0x9a
    ole32!SyncStubInvoke+0x5d
    ole32!StubInvoke+0xdf
    ole32!CCtxComChnl::ContextInvoke+0x19f
    ole32!AppInvoke+0xc2
    ole32!ComInvokeWithLockAndIPID+0x407
    ole32!ThreadInvoke+0x1f0
    RPCRT4!DispatchToStubInCNoAvrf+0x14
    RPCRT4!RPC_INTERFACE::DispatchToStubWorker+0x100
    RPCRT4!RPC_INTERFACE::DispatchToStub+0x62
    RPCRT4!RPC_INTERFACE::DispatchToStubWithObject+0x5b
    RPCRT4!LRPC_SCALL::DispatchRequest+0x436
    RPCRT4!LRPC_SCALL::HandleRequest+0x200
    RPCRT4!LRPC_ADDRESS::ProcessIO+0x44a
    RPCRT4!LOADABLE_TRANSPORT::ProcessIOEvents+0x24a
    RPCRT4!ProcessIOEventsWrapper+0x9
    RPCRT4!BaseCachedThreadRoutine+0x94
    RPCRT4!ThreadStartRoutine+0x24
    kernel32!BaseThreadInitThunk+0xd
    ntdll!RtlUserThreadStart+0x1d

    We break into the function which calls into the HTTP.sys at the kernel mode to delete the SslBindingInfo Registry key.

    Steps to reproduce

    We can reproduce this issue at will by adding the following under <CustomMetadata> section of your applicationhost.config file

    <key path="LM/W3SVC/X">

    <property id="5506" dataType="Binary" userType="1" attributes="None" value="oXiHOzFAMOF0YxIuI7soWvDFEzg=" />

    </key>

    and then launching any application which requires ABO Mapper, for e.g. launching Inetmgr6.exe or enumerating using ADSUTIL VBscript.

    Resolution

    This property is a legacy feature from IIS 6 days and ported onto IIS 7+. If we have correctly added the certificate in the IIS manager this specific property with id 5506 is not needed. Search for the above entry in your ApplicationHost.config file and remove the property in case you are seeing the above issue.

    PS: I have got the above issue/resolution recently documented as a fast-publish KB article 2025598.

    till next time

Page 1 of 1 (2 items)