Rob Kraft's Software Development Blog

Software Development Insights

Archive for May, 2020

How To Configure Your .Net Core Web App to Run OutOfProcess and Publish It That Way

Posted by robkraft on May 31, 2020

I put a .Net Core web site on my new webhost, SmarterASP.Net and published it from within Visual Studio 2019 with no problems. A few weeks later I added a second .Net Core web site to my account and published it the same way then discovered my sites were returning 500.34 and/or 500.35 errors.

Both of my sites are running in the same App Pool on IIS and are using the defaults for .Net Core 3.1 which means they are runnning “inprocess”. But we can only have one .Net Core 3.1 app running “inprocess”. I needed to move both of them to run “outofprocess”, which means they will run on Kestrel instead of IIS. I have no concerns about that, but I had trouble figuring out how to tell my “publish” profile in Visual Studio that I wanted the deployed site to run OutOfProcess. There is an option in the Project Properties Debug tab, but that does not carry over to the web.config that gets created when I publish my app through Visual Studio using FTP.

To change a .Net Core 3.1 app to OutOfProcess so that when you publish it the web.config for the published site also uses OutOfProcess, you need to manually edit your .csproj file and add an attribute AspNetCoreHostingModel in the Property Group that has your TargetFramework:

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <AspNetCoreHostingModel>OutOfProcess</AspNetCoreHostingModel>
  </PropertyGroup>

Posted in Coding, Dev Environment | Leave a Comment »

How To Reduce The Connection Pools For a .Net Web Site Hitting Multiple Databases

Posted by robkraft on May 9, 2020

Microsoft’s connection pooling built in to the .Net Framework greatly improves performance by allowing hundreds of database calls to share the same pool of connections used to connect to the database. The .Net Framework creates a new pool of connections for each unique connection string. This works fantastic when your web application connects to a single database and all your connections use the same connection string. It even works well if you have two or three different connection strings, but for each unique connection string value, the .Net Framework creates a new connection pool. If your web site has dozens of different databases to connect to, you may find your application creates dozens of connection pools which begins to consume a lot of resources on both the web server and SQL Server.

There is a fairly easy way to eliminate this problem, and that is to use the ChangeDatabase method on the Connection object.

Our web application was connecting to one of fifty different databases that all reside on the same instance of SQL Server. Each of our clients has their own database. But our application was creating fifty different connection pools. We were able to have all the clients share a single pool of connections by first connecting to a neutral database, then redirecting the connection to the desired database.

We created a new database on the server, called Central, then we had all the connections to the database first connect to Central as the Initial Catalog. The next step was to call ChangeDatabase to switch to the desired database. This technique did not create a new connection to SQL Server and did not create a new connection pool on the .Net client. Microsoft documentation mentions this technique here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases , but their example code does not show the ChangeDatabase method, which is more efficient than their example.

In a nutshell, you can do this:

connection.Open();
connection.ChangeDatabase(“Client1”);

I created a simple Windows Form App to test the idea.  The test app runs a query to return the name of the current database it is in.  Also, on the SQL Server I run a query to see all the connections that exist.  Doing this I was able to prove that using ChangeDatabase did not create a new connection to SQL Server and my .Net SQL Connection Object was pointing at the correct desired database.  Sample code, query, and output is below:

Sample C# .Net Forms App:

using System;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SQLConnectionTest
{
	public partial class Form1 : Form
	{
		//https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases
		private void button1_Click(object sender, EventArgs e)
		{
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=model;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=msdb;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{ 
				connection.Open();
				connection.ChangeDatabase("TempDb");
				RunSQL(connection, "select DB_NAME()");
			}

			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				connection.ChangeDatabase("TempDb");
				RunSQL(connection, "select DB_NAME()");
				connection.ChangeDatabase("model");
				RunSQL(connection, "select DB_NAME()");
			}
		}

		public static void RunSQL(SqlConnection connection, string queryString)
		{
			SqlCommand command = new SqlCommand(queryString, connection);
			try
			{
				SqlDataReader reader = command.ExecuteReader(); 
				if (reader != null)
				{
					while (reader.Read())
					{
						Console.WriteLine("\t{0}", reader[0]);
					}
					reader.Close();
				}
			}
			catch (Exception ex){Console.WriteLine(ex.Message);}
		}
		public Form1()
		{
			InitializeComponent();
		}


	}
}

Output from Running the App written to console:

master
model
msdb
master
tempdb
master
tempdb
model

Query to see connections on SQL Server:

CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’) and ProgramName like ‘.net%’
DROP TABLE #TmpLog
go

Results of running query on SQL Server:

Results1

Posted in Coding, SQL Server | Leave a Comment »

How To Filter SP_WHO to Just The Connections You Care About

Posted by robkraft on May 8, 2020

You can run sp_who, or better yet, sp_who2 to quickly get some information about all the current connections to your SQL Server, but for those of us that run it often you probably prefer that it not show all the internal processes connected to the engine.  You can filter the the results of the stored procs by dumping the query results into a temp table and filtering on the temp table.  Something like this:

CREATE TABLE #TmpLog

(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),

CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)

INSERT INTO #TmpLog

EXEC sp_who2

SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName

FROM #TmpLog

where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)

DROP TABLE #TmpLog

Better yet, you can create your own stored proc in the master database then you can run your stored proc with a short command when desired:

create proc sp_whoMine
as
CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)
DROP TABLE #TmpLog
go

Posted in SQL Server | Leave a Comment »

A C# .Net Dialog For Connecting to SQL Server

Posted by robkraft on May 7, 2020

I created a Windows C# .Net Form and made it an example for anyone that wants to create a prompt for logging into SQL Server that looks at behaves like SQL Server Management Studio and other Microsoft products.

The code can be found here: https://github.com/RobKraft/SQLServerLoginTemplate

The code does not “share” the saved credentials with the Microsoft products. It stores those credentials in User Specific Settings for the specific application. Passwords are encrypted using the Windows Machine key (DPAPI), therefore this code will not work if used on Linux.

Posted in Coding, SQL Server | Leave a Comment »

SQL to Find Who Is Trying To Hack The SQL Server You Exposed To The Internet

Posted by robkraft on May 5, 2020

If you are running a SQL Server exposed to the Internet on the default port 1433 then hackers are probably try to hack in to it.  If the SQL Server is running on a different port it is less likely, but it could still be happening.  If you exposed a SQL Server to the world the hackers on the Internet will find it and will launch repeated login attempts against the ‘sa’ account.

That is a good reason for disabling the ‘sa’ account.

That is also a good reason for renaming the ‘sa’ account.

Most SQL Server admins record the Failed Login Attempts in the SQL Server Error Log.  If you have this option turned on, which is recommended, you may see entries like this:

Login failed for user ‘sa’. Reason: Could not find a login matching the name provided. [CLIENT: 113.64.210.68]

You can write an SQL to read the SQL Server ErrorLog and extract all the IP addresses that are attempting to login to your server using the ‘sa’ account.  You can then provide that list to your firewall to blacklist them, although a whitelist of known good addresses is a much better approach.  After you blacklist those IP Addresses, come back the next day and check your logs again because you will probably find a whole bunch of new IP addresses doing the same thing.  This game of ‘whack-a-hacker’ can be played for a long time before the hackers run out of IP addresses.  By the way, if you are going to try to blacklist the bad IP addresses, I recommend blocking entire blocks like 113.64.*.*, not a single IP Address at a time.

Here is an SQL to get you started with reading and filtering your SQL Server Error Logs.  Notice this script only looks for failed login attempts using the ‘sa’ account.  If you look at your logs, you will find failed login attempts for other accounts too.  Happy Hunting.

CREATE TABLE #TmpLog
(LogDate datetime, ProcessInfo VARCHAR(150), Text VARCHAR(max))
INSERT INTO #TmpLog
EXEC sp_readerrorlog
SELECT replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ',''), 
count(*)
FROM #TmpLog where Text like '%Client%' and Text like '%''sa''%' group by
replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ','')
having count(*)>10
order by 2 desc
DROP TABLE #TmpLog

 

 

Posted in Security, SQL Server | Leave a Comment »