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.
Now in order to export the table content into an XML form we need to use FOR XML PATH as below:
FOR XML AUTO
FOR XML RAW
FOR XML PATH
FOR XML PATH('Customer')
If you want to wrap the content under a specific ROOT node use the following:
It may not be something new for the SQL folks but this was something new to me.
till next time….
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.
Here is how it looks if you have the proper binding set at the http.sys level in the Registry.
Note: Should the certificate be changed, the binding will look the same.
If you are experiencing the above problem it could be related to the following <customMetadata> tag in your ApplicationHost.config.
<property id="5506" dataType="Binary" userType="1" attributes="None" value="oXiHOzFAMOF0YxIuI7soWvDFEzg=" />
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:
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
and then launching any application which requires ABO Mapper, for e.g. launching Inetmgr6.exe or enumerating using ADSUTIL VBscript.
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