Connecting to an untrusted domain with credentials

Support for all customers who have purchased a PrimalSQL product license. This forum does not offer a response time guarantee.
Forum rules
DO NOT POST SUBSCRIPTION NUMBERS, LICENSE KEYS OR ANY OTHER LICENSING INFORMATION IN THIS FORUM.
Only the original author and our tech personnel can reply to a topic that is created in this forum. If you find a topic that relates to an issue you are having, please create a new topic and reference the other in your post.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
orders@getsouthern.com
Posts: 4
Joined: Thu May 09, 2019 6:25 pm

Connecting to an untrusted domain with credentials

Post by orders@getsouthern.com » Fri May 17, 2019 1:29 pm

To help you better we need some information from you.

*** Please fill in the fields below. If you leave fields empty or specify 'latest' rather than the actual version your answer will be delayed as we will be forced to ask you for this information. ***

Product, version and build: Primal Script
32 or 64 bit version of product: 64
Operating system: W10
32 or 64 bit OS: 64


I am trying to connect to my test MSSQL server on an untrusted domain. I have appropriate credentials, but I am not able to connect. I modified the connection string to use the full <domainName>\<username> with a password.

Data Source=<<FQDN>;Initial Catalog=;Integrated Security=True;User ID=<domain>\<uid>;Password=<pwd>

This is untrusted because we do not want test stuff on our production domain.

How do I connect?

-Stephen

User avatar
mxtrinidad
Site Admin
Posts: 294
Joined: Sun Mar 03, 2013 12:42 pm

Re: Connecting to an untrusted domain with credentials

Post by mxtrinidad » Fri May 17, 2019 2:05 pm

First, can you provide?

1. SQL Server Version.
2. Are your credentials Windows? Or, SQL Server Authentication.
3. Is this a new server? Meaning, if this is a new server installation, or you have previously connected to successfully.
4. Can you ping the server?

:)

User avatar
mxtrinidad
Site Admin
Posts: 294
Joined: Sun Mar 03, 2013 12:42 pm

Re: Connecting to an untrusted domain with credentials

Post by mxtrinidad » Fri May 17, 2019 2:16 pm

I forgot to ask... Is this on a PowerShell script? Can you post a sample of the script?

orders@getsouthern.com
Posts: 4
Joined: Thu May 09, 2019 6:25 pm

Re: Connecting to an untrusted domain with credentials

Post by orders@getsouthern.com » Sun May 19, 2019 8:16 am

MS SQL Server version is 2016.
I am using Windows credentials.
This server is not new and I can also connect to it (on the server) through the SQL Manager.
I can ping the server. Since it is on an untrusted Windows AD domain, I need to use the FQDN to access it. I am doing that in my connection credentials.

I can ping it by FQDN from my machine.

This is not through PS. I just installed PrimalSQL and I am trying to make my initial connection to the server. I am getting a failure at this point.

I posted my connection string from the PrimalSQL login.

User avatar
mxtrinidad
Site Admin
Posts: 294
Joined: Sun Mar 03, 2013 12:42 pm

Re: Connecting to an untrusted domain with credentials

Post by mxtrinidad » Mon May 20, 2019 7:00 am

Thanks for your response!

So, it's PrimalSQL connection strings.

Have you tried using the untrustedComputerName\username with the password in the connection string?

I would suggest to use a SQL Server authentication. As this is a dev server, then it shouldn't be a problem to use mixed-authentication. This way you could use the SQL Server Authentication with "sa" account or by creating a userid/pwd, then try it the connection string. This way you can also connect remotely using SSMS on another machine.

By the way, in SQL Server Configuration Manager, under the "SQL Server Network Configuration", make sure to enable the TCP-IP protocol so that the Sql Server engine can be reached across the network.

Just an FYI... To use only Windows Authentication, you could create another domain for Development purpose with its own set of user accounts. Some organization uses this method to keep the environments separatd.

Hope this helps!

orders@getsouthern.com
Posts: 4
Joined: Thu May 09, 2019 6:25 pm

Re: Connecting to an untrusted domain with credentials

Post by orders@getsouthern.com » Mon May 20, 2019 12:05 pm

Thanks for getting back.

The remote server is on a test domain with its own set of username and permissions. That is the username I am trying when connecting.

I can look into the mixed authentication, just for testing, but I was hoping to not have to mess with that for now.

I will also double check the SQL network configuration settings.

User avatar
mxtrinidad
Site Admin
Posts: 294
Joined: Sun Mar 03, 2013 12:42 pm

Re: Connecting to an untrusted domain with credentials

Post by mxtrinidad » Mon May 20, 2019 12:37 pm

No worries!

It's been awhile since I work using a workgroup user on an untrusted domain. So, I got the steps for you:

If you want to connect from your dev client machine:
1. Open "ODBC Data Source Administrator (64 bit)".
2. At the "User DSN" tab, click on the "Add" button.
3. Then, select the SqlServer an ODBC driver.
4. Fill out the first screen information with the server name.
5. On the second screen, make sure to leave the "With NT Authentication ..." checked.
6. Click Next, Next, and Finished.

Write down the name of the new DSN and use it in the connection string:
(for example: (User is a workgroup (not AD nor SqlServer user))
Dsn=TestSqlODBC;initial catalog=sampledb1;uid=SAPIEN01\DevUser01;pwd=$MyPwd01!

Then, open PrimalSQL and create a new connection:

1. In the "Database Connection", Click on "ODBC".
2. In "DSN", select the new created ODBC Data Set Name".
3. Now, in the Login add the UntrustedServername\Username
4. Then, add the password.
5. Now, click "Test Connection", then Save.

This should allow you to connect to the untrusted SqlServer machine.

Hope this get you going!

orders@getsouthern.com
Posts: 4
Joined: Thu May 09, 2019 6:25 pm

Re: Connecting to an untrusted domain with credentials

Post by orders@getsouthern.com » Mon May 20, 2019 1:54 pm

The ODBC didn't work. I checked the server and it was set for mixed mode. I was able to connect with a SQL account instead of the Windows account.

Thanks!

User avatar
mxtrinidad
Site Admin
Posts: 294
Joined: Sun Mar 03, 2013 12:42 pm

Re: Connecting to an untrusted domain with credentials

Post by mxtrinidad » Mon May 20, 2019 3:06 pm

It's possible to use ODBC drivers and you'll probably need to set it in both server and your desktop system. Unfortunate, it need some additional work.

This is why some time is better to use the SQL Server Authentication.

:)