IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact

How to Find All References to an Object in a SQL Server Database

You can use the following scripts to find all affected objects that make any references to your search term.

You can either use sql_modules or syscomments to accomplish this purpose. Generally, they should give you the same results, but from time to time, I found that they may give you slightly different results and I haven’t had the chance to look closer how big the difference is. Just to keep in mind.

However, Microsoft has put a warning not to use syscomments in new development work as the feature will be removed in a future version of Microsoft SQL Server.

Using sql_modules:

SELECT DISTINCT OBJECT_NAME(m.object_id) AS "object name", m.*
FROM sys.sql_modules m
WHERE m.definition LIKE '%SearchTerm%'

Using syscomments:

SELECT DISTINCT OBJECT_NAME(c.id) AS "object name", c.*
FROM sys.syscomments c
WHERE c.text LIKE '%SearchTerm%'

sys.sql_modules

Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.

sys.syscomments

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use sys.sql_modules instead. For more information, see sys.sql_modules (Transact-SQL).

Further Reading

How to Search for a String in All Tables in a Database
sys.sql_modules (Transact-SQL)
sys.syscomments (Transact-SQL)
Find all references to an object in an SQL Server database
How to Find a Column Name in SQL Server Database
How to Get Names of All Tables in SQL Server
How to Find a String in SQL Server Stored Procedures

July 7, 2022 Filed Under: How To Tagged With: Microsoft SQL Server, SQL, SQL Server

How to Force HTTPS in IIS

This is a short tutorial on how to force an HTTPS connection to your website in IIS. The way we do it is by adding a URL Rewrite rule that will redirect any unsecured incoming traffic to HTTPS.

Steps

  1. The first step you need to check is if you have URL Rewrite module installed. If you don’t have it installed, you can download it from the link at the bottom of this post.

  2. You can either use the GUI on IIS to set this up, or apply the rules in web.config file of your website. You just need to substitue the target URL with your own.

  3. Open your web.config in a text editor and add the rewrite rule inside the system.webServer child element:

    <system.webServer>
      <rewrite>
        <rules>
          <rule name="Force HTTPS" enabled="true" stopProcessing="true">
            <match url="(.*)" />
            <action type="Redirect" url="https://www.itnota.com" />
            <conditions logicalGrouping="MatchAll">
              <add input="{HTTPS}" pattern="^OFF$" />
            </conditions>
          </rule>
        </rules>
      </rewrite>
    </system.webServer>
    

    Or if you use IIS GUI, it should look something similar to this:

    IIS Rules using URL Rewrite to force HTTPS connection

That’s all there is to force HTTPS connection to your website. In this day and age, this should be the minimum setup for any websites.

Download

URL Rewrite

June 1, 2022 Filed Under: How To Tagged With: IIS, Internet Information Services, Microsoft

How to Check Installed .NET Framework Version

This is a quick way to check an installed version of .NET Framework (as opposed to .NET Core). This works for any .NET Framework 4.5 or later.

We’re doing this by using a PowerShell script to get the value from the registry and a Python script to quickly match the release number to the associated .NET Framework version. This has been used so many times to get the value quickly on production servers, but use it at your own risk.

Steps

  1. On the server where you want to check the .NET Framework version, open a PowerShell window and run the following script:

    (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full").Release
    
  2. Once you see the value, you can check it using the following Python script to see what version of .NET Framework that number corresponds to.

    #! python3
    # Check .NET version based on the release number
    # Reference https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed
    
    releaseVersion = input("\nEnter .NET release number: ")
    
    def checkVersion(release):
    
        relNo = int(release)
    
        if relNo >= 528040:
            print('.NET Framework 4.8')
        elif relNo >= 461808:
            print('.NET Framework 4.7.2')
        elif relNo >= 461308:
            print('.NET Framework 4.7.1')
        elif relNo >= 460798:
            print('.NET Framework 4.7')
        elif relNo >= 394802:
            print('.NET Framework 4.6.2')
        elif relNo >= 394254:
            print('.NET Framework 4.6.1')
        elif relNo >= 393295:
            print('.NET Framework 4.6')
        elif relNo >= 379893:
            print('.NET Framework 4.5.2')
        elif relNo >= 378675:
            print('.NET Framework 4.5.1')
        elif relNo >= 378389:
            print('.NET Framework 4.5')
        else:
            print('No match')
    
    checkVersion(releaseVersion)
    

I’m sure there’s a more elegant way to do this by just running one script to do all these things but this is provided as it is.

Further Reading

How to Check Installed .NET Core Version
How to: Determine which .NET Framework versions are installed
How to Install Python on Windows Server

April 12, 2022 Filed Under: How To Tagged With: .NET, PowerShell, Python

How to Remove .NET Runtime and SDK on Mac

Check Installation

Determine which version is installed with the command line.

According to Microsoft documentation, we need to remove the SDKs and runtimes separately:

Pay attention to the difference in installed locations between the Intel Mac vs. Silicon M series chip.

Arm-based mac: /usr/local/share/dotnet/
Intel-based mac: /usr/local/share/dotnet/x64/dotnet/

In this example, I want to remove the .NET 6 RC on an M1 silicon Mac from previous test.

% dotnet --list-runtimes
Microsoft.AspNetCore.App 6.0.0-rc.1.21452.15 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 6.0.0-rc.1.21451.13 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

% dotnet --list-sdks
6.0.100-rc.1.21463.6 [/usr/local/share/dotnet/sdk]

Uninstall .NET

From the example above, we’ll remove all instances. All these commands can be entered all at once in the Terminal, it will ask you to enter sudo password just once.

sudo rm -rf /usr/local/share/dotnet/sdk/$version
sudo rm -rf /usr/local/share/dotnet/shared/Microsoft.NETCore.App/$version
sudo rm -rf /usr/local/dotnet/shared/Microsoft.AspNetCore.All/$version
sudo rm -rf /usr/local/share/dotnet/shared/Microsoft.AspNetCore.App/$version
sudo rm -rf /usr/local/share/dotnet/host/fxr/$version

That’s it.

You can re-run the dotnet –list-runtimes and dotnet –list-sdks to verify that there’s no longer any .NET installed. You should see something similar to this:

% dotnet --list-runtimes                          
A fatal error occurred. The folder [/usr/local/share/dotnet/host/fxr] does not exist

% dotnet --list-sdks
A fatal error occurred. The folder [/usr/local/share/dotnet/host/fxr] does not exist

Now you can install the new version for your .NET development.

Further Reading

How to remove the .NET Runtime and SDK
How to Check Installed .NET Core Version
Trying .NET 6 Preview on macOS Silicon M1

March 15, 2022 Filed Under: How To Tagged With: .NET, macOS, Microsoft

How to Solve Intermittent 403 Error in IIS

Symptoms

You can load the page just fine, but after going through the pages quickly or by opening several tabs at once, you see a 403 error:

IIS 403 Error Forbidden Access is denied

403 - Forbidden: Access is denied.

You do not have permission to view this directory or page using the credentials that you supplied.

This is a bit misleading since you might right away think that the you do not have permission to access the web page or the folder. But you know you’re authenticated because you could see the page a few seconds ago and the problem is intermittent. So why do you get 403 Forbidden: Access is denied?

What to Check

The first thing you want to check is the IIS log and look for the specific error code (403) with the timestamp when you see the 403 error. You might see something similar to the following:

2022-02-02 22:33:58 10.20.128.70 POST /ResultPage.asp - 80 - 192.168.1.25 Mozilla/4.0... https://www.itnota.com/CheckPage.asp 403 501 0 0

Open up the IIS log in a text editor and search for ” 403″ (without quotes). A leading whitespace is added to narrow down the search. You can also use regular expression to be precise but for this exercise, I think it’s an overkill.

IIS Log Search for 403 501 error in a text editor

One key thing we need to pay attention to is to check the whole error code by looking the one next to the 403 → 501. So to be exact, the error code is actually 403.501.

If you check the definition of this error here, you’ll soon find out this error has nothing to do with permission in the traditional sense of how we understand it:

403.501 - Forbidden: Too many requests from the same client IP; Dynamic IP Restriction Concurrent request rate limit reached.

This is the real issue and it’s easier to fix once we’ve figured out that we need to look at the Dynamic IP Restriction.

So now we have three options:

  1. Disable Dynamic IP Restriction.
  2. Increase the Maximum number of concurrent requests.
  3. If your connection comes from the same IP address (i.e. F5), then you can create a whitelist based on its IP address.
  4. Maybe four, as you can combine option 2 and 3 if needed.

Whether you choose option 1, 2, or 3, all the settings are in the same location in IIS.

Steps

  1. Launch IIS Manager and on the left pane window, select the site that you want to modify.

  2. In the middle window, double-click on the IP Address and Domain Restrictions.

    IIS Settings IP Address and Domain Restrictions

  3. If you want to do either option 1 or 2, click on Edit Dynamic Restriction Settings… on the right window pane.

    IIS Edit Dynamic Restriction Settings Maximum Concurrent Requests

  4. Option 1: To disable the Dynamic IP Restrction, uncheck all the checkboxes and click OK.

  5. Option 2: Modify the number in the Maximum number of concurrent requests: and still leave the Deny IP Address based on the number of concurrent requests checked. Then click OK.

  6. Option 3: You can either leave the Dynamic Restriction Settings alone, or you may combine that setting with the whitelist as well.

  7. In IP Address and Domain Restrictions window, click on Add Allow Entry… on the right window pane.

    IIS Add Allow Entry window on IP Address and Domain Restrictions settings

    Note: All your modification is saved in applicationHost.config file in the server as indicated on the bottom of the IP Address and Domain Restrictions window.

  8. Add the IP Address you want to allow entry that’s not limited by the Dynamic Restriction Settings in the Specific IP address: textbox. Or you can enter a range of IP addresses under the IP address range: textbox. Then click OK.

    IIS IP Address and Domain Restrictions - Add Allow Entry

Additional Note

As mentioned earlier, all the settings we did above is saved applicationHost.config file. The file can be found in the following directory:

%windir%\system32\inetsrv\config

And all the steps above can be skipped if you edit the file using a text editor. I personally like to use GUI to prevent typos so just be aware of the risk of editing this file by hand.

  <location path="##Your-website-name-in-IIS##">
    <system.webServer>
      <asp appAllowClientDebug="true" appAllowDebugging="true" />
      <security>
        <ipSecurity>
          <add ipAddress="192.168.1.25" allowed="true" />
        </ipSecurity>
        <dynamicIpSecurity>
          <denyByConcurrentRequests maxConcurrentRequests="1" />
          <denyByRequestRate maxRequests="20" />
        </dynamicIpSecurity>
      </security>
    </system.webServer>
  </location>

That’s it.

Once you saved all the settings, the new change should take effect immediately.

Further Reading

The HTTP status code in IIS 7.0 and later versions
IIS 8.0 Dynamic IP Address Restrictions
Using Dynamic IP Restrictions
IIS Dynamic IP Restrictions whitelist

February 3, 2022 Filed Under: How To Tagged With: IIS, Internet Information Services, Microsoft, Windows Server

« Previous Page
Next Page »
Buy me a coffee Support this site
Buy Me a Coffee?

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • Tips and Tricks
  • Uncategorized
  • Use Case
  • WordPress
  • Writing

Recent Posts

  • How to View Stored Procedure Code in SQL Server
  • How to Find a String in SQL Server Stored Procedures
  • How to Remove Cached Credentials without Rebooting Windows
  • ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency
  • How to Search for a String in All Tables in a Database

Recent Posts

  • How to View Stored Procedure Code in SQL Server
  • How to Find a String in SQL Server Stored Procedures
  • How to Remove Cached Credentials without Rebooting Windows
  • ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency
  • How to Search for a String in All Tables in a Database

Tags

.NET .NET Core AdSense ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack Linux macOS Microsoft Microsoft SQL Server MVC PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2025 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure