BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Reddy_GS
Calcite | Level 5

Hi,

I have three different data bases in one SQL - server and I have one service accont and password for this serve which user can connect to all three data bases using same service account.

I need to register all these data bases into Metadata through Managemenet console(9.4).

I have created one new library and regaisterd first data base.

My question are here:

Assume server Name: SQL1234

1. How do I register second and third data bases which are on same SQL server and which is havig same service account and same password.

2. HOw can I Include second database under the same server name in management console.

3. how can I create authoriztion access to all thress data base which are having same service account and password.

Appreciate you support in this matter

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

SQL Server tables are defined like this:

[ServerName].[DatabaseName].[SchemaName].[Table]

We have found that defining only the ServerName level in SMC Server Manager works best for us. You can do this either through a connection string like this:

"server=ServerName;DRIVER=SQL Server;user=UserName;password=MyPassword" or through a datasrc definition. We prefer the connection string as then you don't need to define ODBC sources in the ODBC Administrator.

Then in your Data Library definition you add the options QUALIFIER = DatabaseName and SCHEMA = SchemaName to have your data library list all of the tables for a particular schema.

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

In SMC servers are defined using the Server Manager plugin, and databases are defined under Data Library Manager. Multiple data libraries can point to the same server definition.

The server definition can have a single server account and password to connect to any database on that server.

A data library that connects to an external database contains a server tab under properties for identifying the server name.

How do you connect to SQL Server? If you use ODBC / OLEDB you just create a data library of this type in the new data library wizard.

Reddy_GS
Calcite | Level 5

Hi Kiwi,

I got it what you saying but How can I defined both the data base names together when I adding new server on to a server manager plugin since

do i need to give name of the data base like this format: database1;database2 in datasrc: field ?

SASKiwi
PROC Star

SQL Server tables are defined like this:

[ServerName].[DatabaseName].[SchemaName].[Table]

We have found that defining only the ServerName level in SMC Server Manager works best for us. You can do this either through a connection string like this:

"server=ServerName;DRIVER=SQL Server;user=UserName;password=MyPassword" or through a datasrc definition. We prefer the connection string as then you don't need to define ODBC sources in the ODBC Administrator.

Then in your Data Library definition you add the options QUALIFIER = DatabaseName and SCHEMA = SchemaName to have your data library list all of the tables for a particular schema.

Reddy_GS
Calcite | Level 5

Hi Kiwi,

It works now, thank you very much for you valuable time

Kurt_Bremser
Super User

Disclaimer: I can only speak from my experience with DB/2.

There you have what is called an "instance" to connect to a database/set of databases. Every instance is defined in UNIX with a separate userid that has the configuration in its home directory; environment variables are used to hand off the instance to SAS. Since there is only one set of environment variables, a single SAS session/server definition can use only one instance.

So _may_ be forced to define several logical workspace servers to have access to all databases.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

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.

Discussion stats
  • 5 replies
  • 1836 views
  • 0 likes
  • 3 in conversation