-
Notifications
You must be signed in to change notification settings - Fork 225
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Connect-SQL : Failed to connect to SQL instance #2041
Comments
I wonder if there is an inner exception that happens that are not shown in the output 🤔 Suggest looking at the code that outputs the error message It might be that |
I've found the source of the issue, it's upstream, not with SqlServerDSC :
I'm trying to connect to a CloudSQL instance, and the SMO object will not accept FQDN longer than 128 characters for the Connect() method. Not sure why, but it's an upstream issue. This will be an issue for anyone trying to manage a SQL instance in Cloud SQL, and I assume it would be a likely issue in Azure, AWS or other cloud providers : you do not manage the DNS record for the MSSQL server, it is automatically generated from some environment parameters (in the case of GCP, product, zone, project, instance), resulting in a long FQDN. If you wanted to take that into account to throw a specific error message, you would indeed find the detail of the above exception in A workaround for that problem that should still allow for Kerberos authentication is to set a shorter CNAME record pointing to the full A/AAAA record of the MSSQL server Going through that, I've found another obstacle related to GCP Cloud SQL (potentially other cloud providers) that could be better managed by SQLServerDSC (tell me if you'd like me to open a new Issue for that) : Cloud SQL is configured to listen to TCP/1433, but the usual procedure from the client for named instances is apparently to initiate a connection to UDP/1434 to determine a TCP port to use (if I read the docs correctly). Unfortunately, Cloud SQL does not listen to UDP/1434, resulting in timeout. As far as my research went, it cannot be configured to do so However, the client behaviour can be changed by forcing connection on a specific TCP port. This is achieved by using the following address : |
Sorry to hear about the length issue. Did not know about that. Maybe you can open an issue in SqlServer repo or sqlmanagementobjects repo.
It would have been easy to add if there were all class-based resource, then we could have added two new properties ( |
Problem description
When running a SQL configuration (in my case, the SQLLogin DSC resource), I'm met the below error message saying it failed to connect.
Note I was initially using Ansible with ansible.windows.win_dsc, but the below error is returned from recreating the configuration with DSC locally on the computer where I was running the ansible configuration. It still fails for the same reason. The server name is a FQDN with a record in Microsoft AD.
While that error happened using SQLLogin, I've traced it to the Connect-SQL function https://github.com/dsccommunity/SqlServerDsc/blob/23d76847a1c99324eae6fe4a0faf8a7ac24e41fb/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1#L569 so I imagine the issue could be due to the upstream lib Microsoft.SqlServer.Management.Smo.Server, but I'm not sure. It does not even attempt to open a connection
I've tried specifying dbatools as SMODefaultModuleName, no success (with or without). Here' s the configuration :
Verbose logs
How to reproduce
Context is a windows VM running in GCP Compute Engine (google provided image) joined to a managed Microsoft AD, connecting to a Cloud SQL (MSSQL) instance on the same VPC (all network requirements verified, SSMS works fine on the same computer)
Expected behavior
Successful connection to the database instance
Current behavior
Fails to connect.
Does not even attempt a DNS request to resolve the server FQDN, even though it is properly returned by the verbose output. Does not initiate a TCP connection with the server (verified with wireshark during the request)
SSMS works fine from the same computer, same creds
Suggested solution
Nope
Operating system the target node is running
PowerShell version and build the target node is running
Module version used
The text was updated successfully, but these errors were encountered: