Architecting, installing and maintaining your SAS environment

Configure Servers in SAS Management Console (SQL server)

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Configure Servers in SAS Management Console (SQL server)

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


Accepted Solutions
Solution
‎10-26-2017 02:59 AM
Trusted Advisor
Posts: 1,436

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

[ Edited ]
Posted in reply to JuanDaniel

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&docsetVe...

 

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

 

View solution in original post


All Replies
Super User
Posts: 3,309

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

[ Edited ]
Posted in reply to JuanDaniel

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

Contributor
Posts: 35

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

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

Solution
‎10-26-2017 02:59 AM
Trusted Advisor
Posts: 1,436

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

[ Edited ]
Posted in reply to JuanDaniel

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&docsetVe...

 

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

 

Contributor
Posts: 35

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

Posted in reply to JuanS_OCS

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?

 

Trusted Advisor
Posts: 1,436

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

Posted in reply to JuanDaniel

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).

Contributor
Posts: 35

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

[ Edited ]
Posted in reply to JuanS_OCS

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

Trusted Advisor
Posts: 1,436

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

Posted in reply to JuanDaniel

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!

Contributor
Posts: 35

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

Posted in reply to JuanDaniel
Sorry! I am new in this place! Thx
Trusted Advisor
Posts: 1,436

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

Posted in reply to JuanDaniel

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 256 views
  • 8 likes
  • 3 in conversation