question

RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 asked GeethaThatipatri-MSFT commented

Azure SQL Edge on Mac M1 using Docker

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, Action`1 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>b0(Task`1 )
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


dotnet-adazure-sqldatabase-edge
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RajithAlwis-8786 Thank you for posting your Question on Microsoft Q&A and for using Azure services.

Could you please clarify a bit your issue? Are you trying to connect to Azure SQL edge from data studio on a mac using docker?
Or you are trying to connect to SQL server from data studio on a mac using docker?

Regards,
Oury

0 Votes 0 ·
RajithAlwis-4920 avatar image
0 Votes"
RajithAlwis-4920 answered GeethaThatipatri-MSFT commented

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/

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

  2. 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".

  3. 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






· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for visiting the Microsoft QA forums! Have a great day!

Regards
Geetha

0 Votes 0 ·
RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered

Hi Oury

I am trying to connect the Azure SQL edge from data studio on a mac using docker.

Docker version 4.4.2
Azure Data Studio Version: 1.34.0
Mac M1 ARM64

Kind Regards
Rajith

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeethaThatipatri-MSFT avatar image
0 Votes"
GeethaThatipatri-MSFT answered

Hi @RajithAlwis-8786 Thanks for clarifying, I tried to connect in Mac M1 with Docker version 4.4.2, Azure Data Studio Version: 1.34.0, and I was able to connect successfully.
Please check the below steps if it might help to fix your issue.

  1. On Mac OS terminal download this image by using the following command.
    docker pull mcr.microsoft.com/azure-sql-edge

181119-image.png

2,you can see the image downloaded in Docker Desktop
181163-image.png


image.png (233.7 KiB)
image.png (111.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeethaThatipatri-MSFT avatar image
0 Votes"
GeethaThatipatri-MSFT answered GeethaThatipatri-MSFT edited
  1. Once the image is downloaded-go to the terminal again and run the below command

docker run --cap-add SYS_PTRACE -e ‘ACCEPT_EULA=1’ -e ‘MSSQL_SA_PASSWORD=StrongPwd123’ -p 1433:1433 --name SQLServer -d mcr.microsoft.com/azure-sql-edge


181212-image.png

  1. Once the command is successful, go back to Docker Desktop and click on the container option. There you will find the container running with the same name that we have used above.

181202-image.png

  1. Now open Azure Data Studio and click New Connection for setting up the SQL Connection.
    181221-image.png

181144-image.png

Please let us know if this helps

Regards
Geetha


image.png (80.3 KiB)
image.png (247.5 KiB)
image.png (622.3 KiB)
image.png (136.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered GeethaThatipatri-MSFT commented

Hi
yes, I did that number of times. now again.. I am still getting the same error.

is it to do with the server name? docker is installed on my mac.


181833-screen-shot-2022-03-10-at-73002-pm.png




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 - 36559399)
---> System.ComponentModel.Win32Exception (365593993): Unknown error: 365593993
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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>b0(Task`1 )
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:f9341ccf-241e-48ed-a26b-f31efd98fbd7
Error Number:365593993,State:0,Class:20



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, @RajithAlwis-8786 Thanks for sharing the screenshot.
Can you please share the docker run command you used to execute?

Regards
Geetha

0 Votes 0 ·
RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered GeethaThatipatri-MSFT edited

Hi @GeethaThatipatri-MSFT
Please see the command below and the screenshot is attached.

docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=pa55w0rd@' -p 1433:1433 --name SQLServer -d mcr.microsoft.com/azure-sql-edge

docker SQL log also attached.

Thank you very much.

Rajith

182068-screen-shot-2022-03-11-at-34947-pm.png182134-docker-sql-log.txt


182000-screen-shot-2022-03-11-at-34616-pm.png



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, @RajithAlwis-8786 Thanks for sharing the screenshot,
I see that you did not mention the same server you used to connect in the studio, can you please update the server name and retry



![182209-image.png][1]

Regards
Geetha
[1]: /answers/storage/attachments/182209-image.png

0 Votes 0 ·
image.png (80.7 KiB)
RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered GeethaThatipatri-MSFT commented

Hi @GeethaThatipatri-MSFT
Its getting an error. Could you please double-check I am not sure that is the case here.. isn't the server on localhost or 127.0.0.1 ? Docker is installed on Mac? these details also on the docker SQL log.



Please see the error..182403-screen-shot-2022-03-12-at-111846-am.png182383-screen-shot-2022-03-12-at-112228-am.png



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, @RajithAlwis-8786 are you trying to connect to localhost? or Azure MySQL server?

Regards
Geetha

0 Votes 0 ·
RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered GeethaThatipatri-MSFT commented

Hi @GeethaThatipatri-MSFT

it doesn't matter if I can connect to any of the env. what I am doing wrong here?

Thanks
Rajith

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RajithAlwis-8786 yes it does not matter but the error message clearly says it does not exist in the subscription, use the same server name from mysql then it should work.

Regards
Geetha

0 Votes 0 ·
RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered

182513-screen-shot-2022-03-13-at-102837-am.png

Hi @GeethaThatipatri-MSFT

how did you get this docker hostname? Thank you



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RajithAlwis-8786 avatar image
0 Votes"
RajithAlwis-8786 answered GeethaThatipatri-MSFT edited

Hi @GeethaThatipatri-MSFT

Thank you for your continuous response. really appreciate that.

As you've mentioned the problem here me to understand the server name that SQL Server instance sits in the Docker. I cannot connect to it using "localhost" or "127.0.0.1"

however, I can connect to the SQL instance in the Docker using the Sudo command. ( I had to change the server name to "SQLEdgeServer1" due to security reasons as I left the password here in my earlier replies)

sudo docker exec -it SQLEdgeServer1 "bash"

I can connect to the server using sudo docker exe, however, I can't create a DB, there are no compatible mssql-tools for Mac.
( https://docs.microsoft.com/en-us/azure/azure-sql-edge/disconnected-deployment)

182504-screen-shot-2022-03-13-at-80440-pm.png



Is there any other way I can find out the server name?

Thanks in advance
Rajith


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, @RajithAlwis-8786 are you looking to find the MySQL server name? the screenshot you mentioned is the MySQL server hostname not the docker hostname
182943-image.png


182962-image.png

Regards
Geetha


0 Votes 0 ·
image.png (329.9 KiB)