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
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
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
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:
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
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
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
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?
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).
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
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!
No worries! 🙂 That's also why we are here, to lend a hand. Thanks!
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.