topic Re: Configure Servers in SAS Management Console (SQL server) in Administration and Deployment

Configure Servers in SAS Management Console (SQL server)

JuanDaniel — Tue, 24 Oct 2017 23:13:50 GMT

Hi!

We have the next architecture, 1 machine (SAS Server) and another machine with a SQL Server. I have the next question:

In the SAS Management Console have I to create 1 Server and 1 library for each Database in the SQL Server? or exist some process to group this connections?

Thx

Re: Configure Servers in SAS Management Console (SQL server)

SASKiwi — Wed, 25 Oct 2017 00:41:50 GMT

You only need to create one server definition in SMC for the SQL Server. You then create a library definition for each database and possibly schema combination, pointing at the one server definition.

This tech paper, while a bit out of date gives a good idea of how to set these up:

http://support.sas.com/techsup/technote/ts802.pdf

Re: Configure Servers in SAS Management Console (SQL server)

JuanDaniel — Wed, 25 Oct 2017 08:36:17 GMT

Thx for the response.

For do this i created an ODBC connection to an SQL Server but in the options I have to select one default dabatase.

When I create a Server in the SMC I have to introduce the ODBC name in the field DataSource, then this server only appoint to the default database in the ODBC?

Can I point to the SQL Server Machine with my server in the SMC and then only create a Library for each database?

Now I can configure for each database:

1 ODBC - 1 Server in SMC - 1 Library

I dont know how to configure:

N ODBC - 1 Server in SMC - N libraries

Re: Configure Servers in SAS Management Console (SQL server)

JuanS_OCS — Wed, 25 Oct 2017 11:52:23 GMT

Hello @JuanDaniel,

If you have N ODBC connections (to one SQL server type and version, say, SQL Server 2008, eg), you will need:

1 ODBC Server definition on SMC - with the SQL Server Definition and version 2008

N connections on this 1 ODBC server. Each SMC connection will point to each ODBC connection.

N Libraries. Each library will use each of the ODBC connections defined under that 1 server.

If you have other ODBC types or versions, only then you will need additional ODBC server definitions, of if the ODBC connection is defined on a different server.

Additionally, I would use Authentication Domains for each connection-library, so you can create user groups for each DB connection, and the user group should have the user/password for the initial DB connection.

These might help as well:

Establishing Connectivity to an ODBC Database Using Microsoft Windows NT Authentication

http://documentation.sas.com/?docsetId=bidsag&docsetTarget=p1t9q2knb9tq5ln101dkrdsa9ymf.htm&docsetVersion=9.4&locale=en

Usage Note 43070: Creating a DBMS library in SAS® Management Console to Access it from a third-party ODBC application using the SAS ODBC driver

http://support.sas.com/kb/43/070.html

Re: Configure Servers in SAS Management Console (SQL server)

JuanDaniel — Wed, 25 Oct 2017 12:54:04 GMT

Thx for your response and sorry for my ignorance.

I had created an ODBC connection to another machine with host an SQL SERVER with database (SQL1, SQL2, SQL3... for example).

When i go to create a new server on SCM I fill the DataSource field (in this case the name of ODBC). The problem is that the ODBC is point to database (specific or default) then i will need define N servers in SCM for N databases (SQL1, SQL2, SQL3).

With the documents that you share with me, i only gets this solution. I need SAS ODBC Driver? or with a normal SQL Server driver it's enough?

Re: Configure Servers in SAS Management Console (SQL server)

JuanS_OCS — Wed, 25 Oct 2017 13:01:37 GMT

Our pleasure @JuanDaniel. And no worries, nobody here knows stuff "because yes".

I am a bit lost. We need first to ensure your connections are compliant to the license you have got in SAS.

If you have SAS/ACCESS to ODBC, on Widows you will need ODBC definitions (and be tested), and on SAS you will need ODBC Server definitions (and connections).

If you have SAS/ACCESS to SQL Server, you will need SQL Server ODBC defs on Windows and SAS.

Besides this, the point is that on SAS you won't need necessarily more than 1 Server definition, only several connections on this Server definition and then the Libraries can point to those connections. Each connection will point to the different DataSource (the ODBC name indeed).

Re: Configure Servers in SAS Management Console (SQL server)

JuanDaniel — Wed, 25 Oct 2017 13:38:48 GMT

Thx!! I solved the problem.

I created new conections for the same Server in SMC and it works!

Thanks for your help @JuanS_OCS and @SASKiwi

Re: Configure Servers in SAS Management Console (SQL server)

JuanS_OCS — Thu, 26 Oct 2017 06:55:53 GMT

Hello @JuanDaniel, would you mind to mark the post that better helped you as Accepted Solution? This would help other users with similar problem as yours. Thanks!

Re: Configure Servers in SAS Management Console (SQL server)

JuanDaniel — Thu, 26 Oct 2017 07:00:24 GMT

Sorry! I am new in this place! Thx

Re: Configure Servers in SAS Management Console (SQL server)

JuanS_OCS — Thu, 26 Oct 2017 07:24:49 GMT

No worries! 🙂 That's also why we are here, to lend a hand. Thanks!