Page 1 of 1

Connecting to an untrusted domain with credentials

Posted: Fri May 17, 2019 1:29 pm
by orders@getsouthern.com
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

Re: Connecting to an untrusted domain with credentials

Posted: Fri May 17, 2019 2:05 pm
by mxtrinidad
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?

:)

Re: Connecting to an untrusted domain with credentials

Posted: Fri May 17, 2019 2:16 pm
by mxtrinidad
I forgot to ask... Is this on a PowerShell script? Can you post a sample of the script?

Re: Connecting to an untrusted domain with credentials

Posted: Sun May 19, 2019 8:16 am
by orders@getsouthern.com
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.

Re: Connecting to an untrusted domain with credentials

Posted: Mon May 20, 2019 7:00 am
by mxtrinidad
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!

Re: Connecting to an untrusted domain with credentials

Posted: Mon May 20, 2019 12:05 pm
by orders@getsouthern.com
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.

Re: Connecting to an untrusted domain with credentials

Posted: Mon May 20, 2019 12:37 pm
by mxtrinidad
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!

Re: Connecting to an untrusted domain with credentials

Posted: Mon May 20, 2019 1:54 pm
by orders@getsouthern.com
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!

Re: Connecting to an untrusted domain with credentials

Posted: Mon May 20, 2019 3:06 pm
by mxtrinidad
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.

:)