IIS Pattern customisation for connecting MSSQL database

ShubhanshuG
Tera Contributor

Hi All, Good day.

I am attempting Service Mapping for an application which uses IIS Server (hosted on Windows Server) and the next layer of the map is a MSSQL DB. The map is successfully created till the IIS Servers but not proceeding ahead to the MSSQL DB layer. After checking the logs we found the OOB IIS Virtual Directory pattern doesn't have a connection section for MSSQL.
As I am very new to the ITOM module, I need some guidance on how to create a connectivity section from IIS to MSSQL.

Thanks!!

1 ACCEPTED SOLUTION

sairamkoruk
Tera Guru

Hi @ShubhanshuG ,

You don’t need to build a connection section from scratch. The OOB IIS Virtual Directory pattern already provides this through its ADO .NET connectivity section. It reads the application’s Web.config, picks up the connection strings, and creates the connection to the SQL Server. In our implementation, the IIS to MSSQL connection came through this section. If your map stops at IIS, that section is either failing or being skipped, and the discovery log will show you which.

sairamkoruk_1-1793636260185.png

Here’s how to narrow it down:

  1. Open the discovery log for the IIS Virtual Directory pattern and expand the ADO .NET connectivity section. Confirm the connection string was found and that the server and database values were parsed.
  2. If no connection was created, find the step whose condition failed. The log shows where it dropped out.
  3. Check where the application stores its connection string. If it’s held outside Web.config (an external config file or app settings, for example), the OOB section won’t pick it up. In that case, extend the pattern rather than modifying the OOB one. Add a connectivity section that reads the file where the connection string actually lives, parses the server and database values, and creates the connection to the SQL Server endpoint the same way the ADO .NET section does. Use the ADO .NET section as your reference, since the connection creation logic is already there.

Work through the ADO .NET connectivity section step by step in the discovery log and find where it fails or is skipped. That step will tell you whether it’s a parsing issue, a condition that isn’t met, or a connection string location that needs the extension above.
Hope this helps. Please mark it as helpful if it does.

 

 

View solution in original post

5 REPLIES 5

sagieratzon
Tera Contributor

Hi @ShubhanshuG ,


To connect IIS to MSSQL, the pattern just needs to read the application's configuration file. Finding the config file and extracting the connection string is the standard way to do this.
Here is a quick, simple way to set it up:
• Step 1: Locate the file – Launch the Pattern Designer in Debug Mode. Look at the IIS variables to find the application's physical path. Inside that folder, look for web.config (or appsettings.json).
• Step 2: Parse the string – Add a Connection Section to your pattern. Use a Parse XML (or JSON) step to read the file and extract the Data Source (SQL Server IP/Host) and Database Name into variables.
• Step 3: Create the connection – Add a Create Connection step, set the type to MSSQL, and plug in your variables. Service Mapping will now automatically jump to the DB layer!

 

Please look at the current connection section in the IIS Virtual Directory pattern for example to understand how it works - https://XXXXX.service-now.com/$sn_pattern_designer.do?authoring_mode=modify&sys_id=28d607dbfead4be38...

 

Also, there is a great course that has labs to create connection sections and generally get along with pattern designer.

https://learning.servicenow.com/lxp/en/it-operations-management/service-mapping-advanced?id=learning...

 

Let me know if you have further questions.

sairamkoruk
Tera Guru

Hi @ShubhanshuG ,

You don’t need to build a connection section from scratch. The OOB IIS Virtual Directory pattern already provides this through its ADO .NET connectivity section. It reads the application’s Web.config, picks up the connection strings, and creates the connection to the SQL Server. In our implementation, the IIS to MSSQL connection came through this section. If your map stops at IIS, that section is either failing or being skipped, and the discovery log will show you which.

sairamkoruk_1-1793636260185.png

Here’s how to narrow it down:

  1. Open the discovery log for the IIS Virtual Directory pattern and expand the ADO .NET connectivity section. Confirm the connection string was found and that the server and database values were parsed.
  2. If no connection was created, find the step whose condition failed. The log shows where it dropped out.
  3. Check where the application stores its connection string. If it’s held outside Web.config (an external config file or app settings, for example), the OOB section won’t pick it up. In that case, extend the pattern rather than modifying the OOB one. Add a connectivity section that reads the file where the connection string actually lives, parses the server and database values, and creates the connection to the SQL Server endpoint the same way the ADO .NET section does. Use the ADO .NET section as your reference, since the connection creation logic is already there.

Work through the ADO .NET connectivity section step by step in the discovery log and find where it fails or is skipped. That step will tell you whether it’s a parsing issue, a condition that isn’t met, or a connection string location that needs the extension above.
Hope this helps. Please mark it as helpful if it does.

 

 

Hi @sagieratzon 
thanks for the reply. Now that I check the logs on the IIS Virtual Directory pattern and at ADO. NET connectivity section, it seems it's not able to get the details from the web.config file. 

on further investigation, I see, the web.config file is missing under the Tracked configuration files related list on the IIS Virtual directory. Also, whenever the Windows Server discovery is done, it never triggers the IIS pattern and the config files are brought over from the IIS Pattern, i think.

Hi @ShubhanshuG ,

You’re right. The config files, including web.config, are collected by the IIS pattern through its Tracked Files, so if the IIS pattern never runs during Windows Server discovery, web.config won’t appear under Tracked configuration files.

sairamkoruk_0-1793702641798.png

 

So the next step is to troubleshoot why the IIS pattern isn’t running:

  1. Check that the Microsoft IIS Server process classifier is active, and that the Horizontal Pattern probe on its Triggers probes list points to the IIS pattern.
  2. Open the Windows server CI and check the Running Processes related list after discovery. Confirm the IIS service process is listed. If it isn’t, the classifier has nothing to match, and the IIS pattern won’t run.

Hope this helps. Please mark it as helpful if it does.