Azure SQL Edge on Mac M1 using Docker

Question

Hello
I know there are multiple threads with a similar issue, however, nothing helped me so far.

---

Issue: unable to access the SQL Server : azure-sql-edge on Docker in Mac M1 ARM64. SQL Server is working as expected I believe.

YAML: password added as a secret.

apiVersion: apps/v1
kind: Deployment
metadata:
name: mssql-depl
spec:
replicas: 1
selector:
matchLabels:
app: mssql
template:
metadata:
labels:
app: mssql
spec:
containers:

• name: mssql
  image: mcr.microsoft.com/azure-sql-edge
  ports:
• containerPort: 1433
  env:
• name: MSSQL_PID
  value: "Developer"
• name: ACCEPT_EULA
  value: "Y"
• name: MSSQL_SA_PASSWORD
  valueFrom:
  secretKeyRef:
  name: mssql
  key: MSSQL_SA_PASSWORD
• name: MSSQL_AGENT_ENABLED
  value: "TRUE"
• name: MSSQL_COLLATION
  value: "SQL_Latin1_General_CP1_CI_AS"
• name: MSSQL_LCID
  value: "1033"
  volumeMounts:
• mountPath: /var/opt/mssql
  name: mssqldb
  volumes:
• name: mssqldb
  persistentVolumeClaim:

claimName: mssql-claim

apiVersion: v1
kind: Service
metadata:
name: mssql-clusterip-srv
spec:
type: ClusterIP
selector:
app: msql
ports:

• name: mssql
  protocol: TCP
  port: 1433

targetPort: 1433

apiVersion: v1
kind: Service
metadata:
name: mssql-loadbalancer
spec:
type: LoadBalancer
selector:
app: msql
ports:

• protocol: TCP
  port: 1433
  targetPort: 1433

---

it was failed to connect 6 times but connected.:

2022/02/26 08:48:53 [launchpadd] WARNING: Failed to connect to SQL because: dial tcp 127.0.0.1:1431: connect: connection refused, will reattempt connection.
2022/02/26 08:48:55 [launchpadd] INFO: Connection opened from 127.0.0.1:56076 to 127.0.0.1:1431

---

I am trying to connect to the SQL Server using Azure Data Studio, however unable to connect.
Connection Type: Microsoft SQL Server
Server: localhost
Authentication Type: SQL Login
User name: sa

---

ERROR:

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - Undefined error: 0)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass48_0.<CreateReplaceConnectionContinuation>b__0(Task1 _)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 312
--- End of stack trace from previous location ---
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 559
ClientConnectionId:c467241e-b3dc-46b7-9206-039ffefc3f2f

Answer 1

This issue has been resolved after painful reviews and help from one of the MS Azure team members.

Env Background: Mac M1 ARM64 (macOS Monterey Version 12.2.1)
Issue:
mcr.microsoft.com/azure-sql-edge installed on Docker with ClusterIP and ingress load balancer on Kubernetes.

The issue was unable to connect to the Azure SQL Edge through "Azure Data Studio" which is equivalent to SSMS (on windows).

However, noted that "Azure SQL Edge" works on Docker in the native state without having any ClusterIP's and Load Balancers.

Solution:

1. make sure Docker installation is compatible with Mac ARM64.
   URL: Mac with Apple chip: https://docs.docker.com/desktop/mac/apple-silicon/
2. enable "Use Docker Compose V2" under the "General" in the Docker.
3. I used YAML to install Azure SQL Edge, Cluster IP, and Load Balancer. make sure app: mssql used in the "SQL Edge" YAML section name given to "Cluster IP" YAML and "Load Balancer" YAML. in my case I had a typo and missing "s", "msql".
   4. note that Ingress service installation is not included here.

apiVersion: v1
kind: Service
metadata:
name: mssql-clusterip-srv
spec:
type: ClusterIP
selector:
app: msql
ports:

• name: mssql
  protocol: TCP
  port: 1433
  targetPort: 1433

apiVersion: v1
kind: Service
metadata:
name: mssql-loadbalancer
spec:
type: LoadBalancer
selector:
app: msql
ports:

• protocol: TCP
  port: 1433
  targetPort: 1433

Answer 2

Hi @Rajith Alwis As per the support team this issue is now resolved here is the changes which you have made from your end
kubectl port-forward <pod name> <server port>:1433
you can try to telnet localhost <server port>, then you can use the Azure Data Studio to connect the Azure SQL Edge using the localhost,<server port> to connect.
For the loadbalancer, normally we need specific cloud Kubernetes supportability to help us do that. Because it need the public IP address assigned to loadbalancer. However, we can use NodePort to just map the container port to node port specifically without any external IP allocated.

Solution:

1. make sure Docker installation is compatible with Mac ARM64.
   URL: Mac with Apple chip: https://docs.docker.com/desktop/mac/apple-silicon/

enable "Use Docker Compose V2" under the "General" in the Docker.

I used YAML to install Azure SQL Edge, Cluster IP, and Load Balancer. make sure app: mssql used in the "SQL Edge" YAML section name given to "Cluster IP" YAML and "Load Balancer" YAML. in my case I had a typo and missing "s", "msql".

note that Ingress service installation is not included here.

apiVersion: v1
kind: Service
metadata:
name: mssql-clusterip-srv
spec:
type: ClusterIP
selector:
app: msql
ports:

• name: mssql
  protocol: TCP
  port: 1433
  targetPort: 1433

apiVersion: v1
kind: Service
metadata:
name: mssql-loadbalancer
spec:
type: LoadBalancer
selector:
app: msql
ports:

• protocol: TCP
  port: 1433
  targetPort: 1433
  Please let us know if the above changes worked for you.

Regards
Geetha