Configure Servers in SAS Management Console (SQL server)

[JuanDaniel]

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

[SASKiwi]

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

[JuanDaniel]

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

[JuanS_OCS · Accepted solution]

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

[JuanDaniel]

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?

[JuanS_OCS]

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

[JuanDaniel]

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

[JuanS_OCS]

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!

[JuanDaniel]

Sorry! I am new in this place! Thx

[JuanS_OCS]

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