There are multiple layers to the security in SQL Server. At the server level the SQL port (default TCP 1433) must be allowed for remote connections in the firewall. From there, the login attempt is made to the database engine. This is where you must have a server level login defined. If not, by default the failed attempt is logged. The database user must be defined and mapped to the login and that user must have the correct object privileges.
If no user is found, this is logged as “failed to open default database” if the initial catalog is defined in the connection string. In windows authentication logins and users are mapped automatically. In SQL Authentication users can become “orphaned” and you will have to remap the login to the user with the sp_change_users_login procedure.
Sometimes when users need access to the data they don’t have a clue what user is actually accessing the data. If the process goes through a web server there are a few options. One of the popular ones is:
1. client logs in with domainuser1 account to their PC
2. vists website that passes domainuser1 to authenticate
3. website application pool runs as domainapp1
4. domainapp1 accesses SQL
Using this method you can easily deny your users direct access to the SQL Server. Or, this way would also enable an easy way for domainuser1 to have a read only SQL account for reporting.
SELECT/UPDATE/etc Privileges can be granted to database users. Below database users you can define schemas and roles and grant privileges to those instead of directly to the user.
So the security hurdles in order are
PC—External Firewall —- Server Firewall—Server Login—Database User—Role—Schema—Object Privilege
In the future I would like to create a Failed Login alert for all of my SQL servers. Also, I would like to log when users get denied access to objects. These would be helpful when debugging connectivity issues.