Best Authentication Method b/w AWS Lambda service and MS SQL Database running on Ec2 instance

Also note that SQL Server supports TLS 1.2 for encrypting all traffic after the initial TLS handshake starting with SQL Server 2008 (https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-1-2-support-microsoft-sql-server#frequently-asked-questions) and TLS 1.3 since SQL Server 2022 (https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/connect-with-tls-1-3?view=sql-server-ver16).

These are the same protocols that are used to access AWS Secrets Manager or Azure Key Vault and the equivalent level of security that would be used to protect a password when it's sent to an AD domain controller to set the password for the user/service account.

For security-sensitive use or to accommodate general zero-trust requirements, encryption in transit can also be enforced on the server side for all users: https://learn.microsoft.com/en-us/sql/tools/configuration-manager/protocols-for-mssqlserver-properties-flags-tab?view=sql-server-ver16#options, in addition to always being possible to request and require on the client side by specifying it in the connection string.

The security benefits of using Kerberos to authenticate to SQL Servers is primarily based on the machine connecting to the database and the SQL Server database server belonging to the same AD forest or another forest with a trust relationship with it. The AD membership provides the client machine with a regularly rotated, secure machine password for connecting to AD securely and authenticating the identity of the AD and AD-connected target resources, without relying on any secret known to a human user. Other mechanisms can be set up on top of that foundation, such as group-managed service accounts (gMSAs), which a service or scheduled task running on a static server could use to authenticate to other services, such as SQL Server databases.

Lambda functions (or Microsoft's own Azure Functions and Logic Apps) run on ephemeral, disposable compute platforms, so the classical paradigm of a long-lived static server belonging to an AD domain doesn't apply, and therefore neither do any of the mechanisms built on top of it.

It's technically possible to emulate a domain-joined client on an ephemeral platform with a great deal of complexity, as you mentioned, but even if you went to that trouble, it would still require a static, long-term computer account password to be known to both your Lambda function and AD to provide the foundation for the trusted connection between the Lambda and AD-connected resources. In other words, the static password of the SQL Server login would be replaced with a static computer account password that you'd be storing in Secrets Manager or other safe place and securing in exactly the same way as you're storing the password for a SQL Server login now. In both cases, security would be equally completely compromised if the password was exposed.

If you are looking to disable SQL Server authentication entirely on the database server, blocking its use for all clients rather than to just the Lambda function, then the benefit from going to the trouble of building the Kerberos emulation on top of the Lambda platform would come from all the other, non-Lambda clients being guaranteed no longer to be using SQL Server logins or connections that aren't mutually authenticated to access the database server, which might make it worth considering. I still wouldn't suggest it as a security improvement, since other users can still use Kerberos to authenticate to the database. The VPC networking platform of AWS ensures that the Lambda function is connecting to the expected database server, avoiding the need to use Kerberos for mutual authentication.

I would advise continuing to use SQL Server authentication to connect from your Lambda function to the database server.

Kerberos Authentication offers significant advantages over SQL Authentication, which transmits passwords in clear text. With Kerberos, passwords are protected through encrypted tickets and session keys, ensuring higher security and mutual authentication between clients and servers. This approach mitigates the vulnerabilities of using SQL Authentication, especially over TLS 1.2, which can be compromised by attacks like GOLDENDOODLE, exposing clear text passwords to potential attackers. Additionally, Kerberos supports Perfect Forward Secrecy (PFS) and integrates seamlessly with Active Directory, making it a robust and scalable choice for enterprise environments.