Security – Page 2 – My Point of View

Protected web api configuration

Like web apps, the ASP.NET and ASP.NET Core web APIs are protected because their controller actions are prefixed with the [Authorize] attribute. The controller actions can be called only if the API is called with an authorized identity.

Consider the following questions:

Only an app can call a web API. How does the API know the identity of the app that calls it?
If the app calls the API on behalf of a user, what’s the user’s identity?

Server is ethernet connected on LAN, means exposed to the internet?

You may have an Internet connection, but you are almost certainly behind a NAT router, not directly connected to the Internet. Normally, that NAT router is the only machine that faces the Internet, has a direct connection, and is under constant attack by numerous bots roaming the IP’s of the Internet.

You only have a local IP for your local network. Only the router has your true IP that is seen on the Internet. When your browser or NTP service (or other Internet need you may have) needs to see something on the Internet, it makes a connection to an Internet server, and your router notes that connection and allows that server to respond, using the associated ports of your connection. The router will route those responses back to your machine, and not any other.

The outside bots and servers cannot attack or connect to your machine, because they can’t even see it, and they don’t know your local IP. The only contact that outside machines can have with your machine is strictly through connections your machine initiates first, through your router.

Now if you *did* want to put your server directly on the Internet, most routers have a setting where they can put any machine into a ‘DMZ’, a special unprotected zone, which means the Internet is directly connected to any machine you choose! And the router won’t block any Internet traffic then, but allow all of it to come through to you.

I would strongly advise you to first disconnect ALL of your drives, and backup your boot drive, because you will be very rapidly attacked! Never use the DMZ unless you have a lot of security experience!

Manage another user (service account) credentials for network access

I have a Windows account that is used for running services (i.e. it’s not intended that any person should log in as that account). Turns out one of the services needs to access a remote network share that’s on a machine in a different Windows domain, and so needs to supply remote credentials to get to that share.

Now if it was me needing to access the remote share, I would simply open Credential Manager, and save the required credentials. But it’s not me, and my understanding of credential manager is it only saves credentials to be used by the logged in user.

I can of course solve this problem. I temporarily elevate the privileges of the service account to allow interactive logins, then I login as that user and use credential manager to store the correct remote credentials. Then I remove the interactive login privileges. But that feels very hacky and not the kind of thing I ought to be doing.

The work around is to log in with your normal user account and then run following in an elevated command prompt;

runas /user:serviceaccountname "%windir%\system32\cmdkey.exe /add:server.domain.com /user:username /pass:password"

For example, if service account need permission on Azure storage then;

runas /user:yourUserName.onmicrosoft.com "%windir%\system32\cmdkey.exe /add:{storageAccountName}.file.core.windows.net /user:Azure\{storageAccountName} /pass:sharedkeyofthestorageaccount=="

You will be prompted for credential. Put in your service account name and password. The credential for the service account will be stored in credential manager and you will be good to go;

Resource

https://superuser.com/questions/537697/manage-another-users-credentials-for-network-access

View effective permission of single user

execute as user = 'SomeUserName' -- Set this to the user name you wish to check
select * from fn_my_permissions(null, 'DATABASE') -- Leave these arguments, don't change to MyDatabaseName
order by subentity_name, permission_name
revert

This will list all effective permission for this user;

Read, Write and Execute permission to SQL Login

I have a web application. This application connect to MS SQL SERVER 2017 for data manipulation (SELECT, UPDATE, DELETE, INSERT) and execute stored procedures. The application also run SQL Agent jobs. I need to create a user in the database to allows application to connect and execute queries, stored procedures and run SQL Agent jobs.

Open SSMS (sql server management studio) login through sysaddmin acount e.g. sa

Make sure “user1” has connect permission to yourDB.

Execute this query

use yourDB
go

GRANT EXECUTE TO user1
GRANT SELECT TO user1
GRANT INSERT TO user1
GRANT UPDATE TO user1
GRANT DELETE TO user1

and also execute this

GRANT ALTER ON SCHEMA::dbo TO user1

where user1 is your user

If we want to allow this user to run sql agent jobs, we need to add it to “SQLAgentOperatorRole”. This role will allow the user to run any job on the server.

Now to SQL Agent permissions;

USE [msdb]
GO
CREATE USER [user1] FOR LOGIN [user1]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [user1]
GO

Make sure user has these permissions in MSDB database;

This is a good article on setting up jobs and an idea to integrate those jobs in UI.

How do I let someone execute a SQL Agent job they don’t own?

for troubleshooting, assign user to “sysadmin” Server Role. Make sure to revoke this permission afterwards.