Architecting, installing and maintaining your SAS environment

Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

Reply
New Contributor
Posts: 4

Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

Hello, 

He recently added a SQL Server DataMart within our domain. EG users need to connect to this data source..

The issue is we have several domains. Our client, EG users, sit in Domain A. But EG does not run the script on the client. It passes it to the SAS server which sits in Domain B .

They need to connect to the DataMart, using Windows Authentication, to a SQL Server machine which sits back in Domain A.

I was thinking this might be resolved by using s Domain_A/sassrv user as my spawner user and granting this user Trusted Delegation.

Has anyone come across this type of infrastructure before.

Super User
Posts: 6,946

Re: Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

I don't think granting something to sassrv will help, because that userid only runs the spawner. The workspace server process runs under the user's id as defined in the metadata account information.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,124

Re: Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

sassrv, by default, will only initiate some special SAS instance, such as pooled workspace server or stored process server. As long as sassrv has an external identity (eg. Windows id), then in theory, it can be used as group identity/ service account for relational database. However, is it a good practice? I doubt that. SAS has this dedicated role for sassrv for a reason. Why not just create a new group ID for this purpose?

Valued Guide
Posts: 3,208

Re: Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

As you think in autorisations domains or call them security providers ....
Knowing and accepting they are there you can handle those.

There are:

- SAS internal security 

  (@saspw) wat is registered and done using the SAS metadata information

- Processes the run on the server OS level.

  For starting those there is a login at the OS level, The user-key with password is found in the metadata.

  The inbound login (default) is often used for that.   The password is cached with the personal user in the sas metadata. (possible leaked)

  The processes run by a service account are having the key and password found at the general servers  group  from where they are shared to  a SP or PWS definition.  

- External connections can be defined on the following ways

  + using is coded with the libname statement. This can be extended to an save OS location using sas-macro.

  + using the authdomain within sas metadatabase storing userids and passwords.         

 

For an external  security provider you are getting the userid-password of that authentication environment. It can be the same key as in other authentication environment that oculd cause confusion. Just keep those security domains as figure to underpin what is happening.

Assumed is that you can access that other domain not being obstructed by firewalls.

Hai-kuo the concepts of security with SAS are not aligned with common security requiements. (standard of good proactice  - owasp - itil - cobit -iso27k00 - sox 404 etc).

Some issues are:

- Group accounts are to be avoided. sasasrv is a group account.

- storing of password should be avoided. This is however common practice in sas

- Being auditable/traceable is a common requirement (SIEM) sas is missing the why of this.

(more..)   Knowing the issue a lot can be done on those. Not an political easy job.    

---->-- ja karman --<-----
Trusted Advisor
Posts: 1,141

Re: Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

Hi Jaime,

as most probably your SAS server is running the services with Local System (just guessing it is a windows server), probably you are the good way, but I believe it is a little more complex than that.

Even if this is not a best practice, for this configuration I would begin leaving sassrv account as it is, and I would create delegation for the server account, register the SPNs, then check if you can reach the DB server through the network, and check if the Kerberos keys/tickets are being generated properly.

Afterwards, you can try to change to service account delegation, instead of the server account. Also with the additional changes: change the user that starts the Object spawner, add special local policies for the user that starts the object spawner and SAS app (if any) and all validations required.

By the way, I am still unsure that this configuration would work.

At least, you can always read some useful information on the internet, the SAS Administration Guide for your SAS version, or SAS &amp; IWA: Verifying Trusted for Delegation Status

Super User
Posts: 3,111

Re: Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

Thanks Juan for the great post. We are going through a SAS 9.4 install ourselves and have the same problem as Jaime except we are on the same domain.

You are right on the money that the SAS services on Windows server run as Local System, so your recommendation to do a Trusted Delegation on this account (Paul Homes' Pxxxxx account I guess - excellent link) is very timely. Was it this delegation alone that worked for you?

Valued Guide
Posts: 3,208

Re: Connecting to a SQL Server datasource via trusted delegation when client, SAS Server and SQL Server are in different domains.

SASkiwi, a trust of the local system account is impossible, as the name implies it is local.
Windows has at least 5 build in system accounts for several purposes. With Unix there is only root (0) and you are seeing to come the (1) coming with SELINUX for security confinement.

Microsoft

User ilinks: Service User Accounts (Windows) (local system, networkservice, local service) at the domain level (domain adminm guest). The local system account is a mighty one on you local machine (like root). LocalSystem Account (Windows)

For groups: Using Default Group Accounts  some policy why not use the domain admin account (root of all machines) Security Watch: Why You Should Disable the Administrator Account. Yep it will probably give you no security questions to solve using that one for all. That approach however should be pre-historic these days. Security Watch: Why You Should Disable the Administrator Account  

In security policies there is "do not use root" and set your limits to the lowest need (do not grant rights that are not needed).

Now you are coming in a with a default SAS installation... ouch it is violating a lot those.  Problems problems.... you can do two two things, change or take over the world tyring to follow SAS defaults or do adjustments to SAS defaults and try to implement them. The local-system-account is used be default.

SAS(R) 9.3 Intelligence Platform: Security Administration Guide  SAS(R) 9.4 Intelligence Platform: Security Administration Guide, Second Edition (Windows Privileges)

Want to run more secure according policies, used dedicated users for sas services and have a sas group in your windows domain.
The "Trusted for Delegation" property of the needed dedicated sas accounts is described.  (at AD-domain level!)

The additional "logon as batch job" for alls spawned processes is described.  (at AD-domain level!)

Instead of documenting and explaining this approach by SAS institute so it is accepted by MS-security architects and admins it is your work (and everybody else) to do this.

Those MS-security guys do not want to implement those risky high priviledged processes when nothing underpinned. Be prepared for the discussions    

Having all your needed account being well defined at the AD-Domain level all servers are tsusted within that.

Wy SAS did follow this pre-historic approach like the own machine root usage?

Well it is easy selling and getting it up on a single machine.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 723 views
  • 0 likes
  • 6 in conversation