Connect MS SQL server with Athena using Lambda connection

Question

- I have MS SQL server running on-prem and I want to take from MS Sql Server somehow get to Athena to query data. When I find data source for MS SQL Server, it needs connection with Lambda.

So after seeing the defined Lambda applications, there is so much information I don't know where to get.
- SecretNamePrefix
- SpillBucket
- DefaultConnectionString
- LambdaFunctionName
- LambdaRoleARN
- PermissionsBoundaryARN
- SecurityGroupIds
- SubnetIds
![Enter image description here](/media/postImages/original/IMzc6pjse6RTShp_Dz9TZRUQ)

I don't see where do we need to setup in order to get MSSQL Server on-prem to connect with Lambda. Anyone have done or have guidelines can you guys share some experience for me? That is really a big help for me right now cause I'm really confused

Gary Mclean · Answer

According to these instructions, you will need to create a JDBC connector to your On Prem SQL Server. You can start with using IP Address instead of host name if you do not have any Route53 Outbound connectors configured.
https://docs.aws.amazon.com/athena/latest/ug/connectors-microsoft-sql-server.html

`sqlserver://jdbc:sqlserver://sqlserver1.hostname:port;databaseName=;${secret1_name}`

Store your SQL Username and Password in secrets manager and reference it in the JDBC Connection String
`{"username": "${username}", "password": "${password}"}`

You will need to ensure you have the appropiate IP Routes in your VPC to your On Prem and vice versa. NACLs will need to allow traffic.
You will need to obtain a Subnet & VPC ID of where you want the JDBC Connector to in initiate the connection from. A security group ID will also be required with an outbound rule allowing access to the On Prem IP Address/Network CIDR

Connect MS SQL server with Athena using Lambda connection

Relevant content