Adding Authentication Domain for SQL Server

[milts]

Hi,

I have set-up a metadata object ODBC server that connects to a SQL Server in SAS Management Console with the following settings:

ODBC Data Source Information (Connection String):

"Driver=SQL Server;Server=MyServer\SRVR;Database=MYDB;"

Authentication Information:

Authentication Type: User/Password

Authentication Domain: mysa (SQL Server account only)

When I create a library in SMC and register tables, a prompt will appear to key in the login. I input the mysa account and password but fails. What account should I put in for this to work? Greatly appreciate any help.

Thanks in advance!

[AndrewHowell]

Ok, too many possibilities, so let's narrow it down:

Three step process:

1. Verify ODBC connection
2. Verify SAS ODBC libname
3. Verify metadata libname definition

1. Firstly, fogetting SAS for the moment, does the ODBC connection itself work? (For example, if on a Windows box, go to the ODBC manager, select the properties of your ODBC connection and test the connection).
2. Assuming you can establish an ODBC connection from your SAS server to the ODBC data source, next execute a standard SAS ODBC libname statement, such as:

   libname MyTest ODBC dsn="My ODBC definition" user="domain\userid" password="mypassword";
   %put &=SYSLIBRC; /* Should be 0 */

3. Assuming a SAS session can create an SAS ODBC libname, now to work out what's wrong in your metadata definition - can you post the library's metadata properties here?

[AnandVyas]

Hi @milts

When you try to register tables for the first time in SMC, there would be no cached login credentials available for SMC to launch Process on the compute which in turn would then connect to Data-base.

So If you are registering for the first time, give the login ID and password for the SAS User account which would launch process on the compute node to then connect to Database.

Kindly note the identity of the same ID in SMC should also be part of the Auth domain group if created any or should have login credentials of the database assigned to it under the Accounts tab.

Hope this helps.

Thanks,
AV

[anja]

Hi,

what is the exact error message you are seeing, and, what user ID are you using to log on to SASMC?

Is it sasadm@saspw, or, your personal ID being admin?

Thanks

Anja

[milts]

Thanks for all the responses. I've managed to solve it.

As per explanation by @AnandVyas, when launching SMC cached credentials are not available yet, so when i go register tables, it asks for a two prompts, first is on the SASApp which is a domain account (Default Auth) that launces the workspace, followed by the prompt which asks for the username and password for the SQL Server (DB Auth).

I didn't notice that prompts initially so I was actually trying to key in the SQL server account when it as still asking for the domain account.