SQL Server connections in Management Console and DI Studio

[snoopy369]

We're working on migrating from one DI Studio server to another DI Studio server, and have run into a knowledge gap: how Servers work for ODBC connections. 

Normally, in DI Studio, we just create the libraries directly using the connection strings (in the DI Studio interface) - DI Studio doesn't separate "server" from "library".  That works fine.  We use connection strings like so:

LIBNAME abcdef ODBC CONNECTION=GLOBAL UTILCONN_TRANSIENT=YES NOPROMPT="server=myserver; DRIVER=SQL Server;Trusted_Connection=yes;database=mydb" SCHEMA=dbo ;

We don't use username/password (and our SQL server doesn't support it, only IWA).  The libname has to exist in metadata, not just in plain code, given DIS's requirements.

When we go to migrate the jobs, the libname comes along for the ride, but it needs us to create a server.  When we try to do that in management console, we can't get that to work properly.

What's the right way to set up a server for an ODBC connection that does _not_ use a DSN, in Management Console?  Do I have to use username/password? 

Thanks!

[KS4NCS]

You can refer to this guide for the setup in SAS Management Console.
https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/p1t9q2knb9tq5ln101dkrdsa9ymf.htm

[snoopy369]

That's certainly what I'd looked at before, but that's why I came here... that assumes a DSN.  

It does look though that we can do basically those steps, but instead of configuring a DSN, put the full configuration string in there.  I'm a little surprised that the Server has to contain the Database as well as the DB Server, as I don't understand the point of separating these two things - why the libname couldn't contain the database and the server just point to the database server - but I'm guessing it's related to SAS shoehorning this into the established SAS infrastructure like everything else. 

[SASKiwi]

All you need to do is define an ODBC server in Management Console Server Manager. Then create an ODBC connection within the server, select Connection String in the Options tab and paste your normal database connection string into the text box. If you are using Trusted_Connection=yes in the connection string then leave your Authentication Method as None.

There is no need to define a database in the connection string, just the SQL server name. You define your database and schema separately when creating a data library using this server definition.

[Patrick]

Two questions

1. If this is a migration then why do you need to change the libnames? Or is the database also changing?

2. What do you mean by: "Normally, in DI Studio, we just create the libraries directly using the connection strings"? With DIS being metadata driven all libraries need to be defined in SAS Metadata. Only user written code allows to not use metadata - and that's always a "last resort" and shouldn't be necessary for libnames. If you really need a libname statement in user written code (i.e. because the library is not pre-assigned) then you really should use the libname META engine to always use the libname metadata definition.

[Sajid01]

This is what has been said in the original question.

"Normally, in DI Studio, we just create the libraries directly using the connection strings (in the DI Studio interface) - DI Studio doesn't separate "server" from "library".  That works fine.  We use connection strings like so".

LIBNAME abcdef ODBC CONNECTION=GLOBAL UTILCONN_TRANSIENT=YES NOPROMPT="server=myserver; DRIVER=SQL Server;Trusted_Connection=yes;database=mydb" SCHEMA=dbo ;

This means there should not be any need to create a server definition in metadata.
Let us start from the basics. As an Administrator I would perform one or more of the following tests depending on the environment
(1) server name and driver details are correct (2) Server is reachable (Using ping) (3)The ODBC driver /dsn exist. (4) Try testing connectivity with a batch SAS code containing the above libname statement. If this fails SAS logs will give an idea why it failed.
I would also do one more thing as a secondary confirmatory test for connectivity. Install SQL Server Management Studio in the machine where SAS is installed and see if it can connect (perhaps the machine where DI studio is installed).