What does Identity Server offer that ASP.NET Core Identity does not

You shouldn't need SQL Server to put an ASP.NET application online. If your site requires database persistence, then yes, you'll need SQL Server installed, but not necessarily on the same server. If your requirements were that "we need to deploy a website", then there's no indication that SQL Server would be a requirement, and I would not expect them to give you access. Many hosts, such as http://discountasp.net, provide hosting for ASP.NET applications and SQL Server databases completely separately.

One solution that you DO have is to host your ASP.NET application on the server of this company (assuming you're still on speaking terms), and host your SQL Server database on another machine somewhere completely different - they don't have to be on the same machine. Your web.config file will let you change your connection string so you can have them separately.

I don't know the specifics of the conversation, but unless you tell them that you specifically need SQL Server (though most web applications do have a database of some kind and you can pretty well assume it's needed), hosting companies still reserve the right to give/sell SQL Server separately. If you have admin access to your hosting machine, I would suggest just installing SQL Server Express unless you need some of the more serious SQL features. Most of the time the reason hosts sell SQL Server databases/install separately is the EXTREMELY hefty license price tag of the the non-express version.

I hope this helps, and leave a comment if I need to go into more detail in a particular area.

I would use sessions. You can store the information you need after they've logged in, be it a token given by the login service or even just an isLoggedIn boolean value.

Then, you can check in pages that need it (Inheriting from a base page class is great for this) that the information is there. If not, kick them back out.

So, in your code that processes the response from the claims-based auth (I do a lot of work with CAS and Shibboleth, two claims-based systems in heavy use in academia), you'd do something like :

if(isSuccessfulLogin)
{
    Session.Add("isLoggedIn",true);
    Session.Add("userId", userIDfromService);
}

Then, in your Page_Load method, you'd just do something like:

if(Session["isLoggedIn"])
{
    // do whatever
}
else
{
    // redirect somewhere
}

These are very simplified, and leave out the details.