Create libname pointing to SQL server database

[Quentin]

Hi All,

As a SAS programmer (not a network admin), can I create a connection to a new SQL server database without having to get a network admin to edit the odbc.ini file?

I think the anwer is no (from reading SAS/ACCESS to Relational Database 9.3).

We have a new SAS server install (on a linux box).  Currently I do not have write access to the odbc.ini file sitting on the server.  So it seems like every time I want to pull data from a new SQL server database, I will have to request a sysadmin edit the odbc.ini file, is that right?

I imagine many BI environments have a defined set of databases, but we get new databases all the time.  So would be nice if I could set up a libname pointing to a database myself.  Would it be realistic for me (lowly SAS programmer) to ask for write permission to odbc.ini, or would most sys admins balk at that?

On a related note, once we get SAS/ACCESS to Oracle Set up, how would this process change?  It looks like it does NOT use odbc.ini file.  So does that mean I should be able to define a libname pointing to an oracle database without asking a sysadmin for help (assuming I have all the permissions I would want in SAS Management Studio, DI Studio, etc?)

Thanks,

--Q.

[shivas]

Hi,

sample odbc.ini file (Database=users,if new database comes we need to add in this file I guess)

Driver=/install/sas/driver/lib/S0msss19.so

Description=DataDirect 4.20 SQL Server Wire Protocol

Address=199.255.255.255,1433

AnsiNPW=Yes

Database=users

LogonID=

Password=

QuotedId=yes

But in oracle you need to set the ORACLE_HOME

environment variable. In addition, you must make sure that the shared library path variable (the

name of this variable is operating system dependent) points to where the Oracle shared libraries

are located. This is required since the SAS/ACCESS Interface to Oracle executable uses Oracle

shared libraries and needs to know where they are located at your site.

For adding database in oracle you need to add in tnsname.ora file eg:

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

SASOPRSK =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = SASOPRSK)

    )

  )

Hope this helps...

Thanks,

Shiva

[Quentin]

Thanks Shiva,

I guess my question is really: As a SAS programmer, should I be able to create a new connection (libref or whatever) to a new SQL server database / Oracle database without involving a network admin?

So for SQL server, looks like the odbc.ini file needs to be revised to create a new connection.  If I have access to SAS Management Console, is there a tool in that will create the necessary updates to the odbc.ini file?  Or do I literally need to hop over to the linux box, and edit the file directly.  If it's the former, it would feel to me like SAS programmers can create connections by themselves (i.e. using only SAS).  If it's the latter, then I would say SAS programmers cannot create new connections by themselves.  Which would be disappointing, as we pull data from new databases all the time.

Same question applies to Oracle.

Thanks again,

--Q.

[Tom]

I know that for Oracle you can put the connection information directly into your LIBNAME (or CONNECT) statement.  You would probably still need to get this information from your DBA.  In terms of managing your tnsnames.ora file that is up to your local IT support policies.  Some sites force everyone to use a common file (perhaps by forcing it out to each PC?) and others will allow you to modify that file, even on machines were user does not have admin rights. But it is better to centrally manage this using TSNAMES . Then the DBA is free to move the database to another server or port without you having to change your SAS code.

proc sql;

  connect to oracle (user='ops$abc' pw=XXXXXXXX

    path=

' (DESCRIPTION =

  (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP) (Host = hostname) (Port = 1553))

    )

    (CONNECT_DATA =

     (SID = dbsid)

    )

   )'

  );

[Quentin]

Thanks Tom,

That's helpful.  Since I will be working in a BI Server environment, sounds like for oracle connections I might just define the full connection info in the libname statement.  I would be creating shared librefs defined in the server metadata.  So even if a DBA moved the database, I would only need to update that one library definition to get the library working for all users.  At least it's good to know that I will (should) be able to create new connections to an oracle database without having to submit a ticket to the help desk.  I assume there will only be one TSNAMES file sitting on the server, and I won't have write access.

Sounds like for SQLSERVER I may be stuck submitting a help desk ticket to have them add lines to the odbc.ini file every time I want to connect to a new database. sigh.

Thanks again,

-Q.

[AlexCurrie]

Connecting to a particular SQL Server database requires an entry in odbc.ini. It sounds like the real issue is how to do that as conveniently as possible for the SAS programmers and the network admin. Since odbc.ini is just a plain text file, one idea is to recreate that file dynamcially. You could keep the components for each ODBC connection (server, database name, etc.) as records in a SAS dataset or SQL Server table. SAS programers would be allowed to add records to that table. Then as needed a SAS program could be run under the appropriate permissions to write out an updated odbc.ini in the required format. This might require setting up a sudo entry on a script that calls the SAS program. Since it sounds like your environment is very dynamic it seems like it would be worth it for all concerned to invest in getting that set up.

[Quentin]

Thanks Alex,

Agree, your solution of having a dynamically generated odbc.ini file (ideally based on a SAS dataset which developers can easily add records to), is a good one.  Or the admins might be more comfortable if they wrote the tool, and forced us to type into ito it.  I could even live with having the non-data driven approach, as long there is agreement that it's reasonable for SAS users to add to the odbc.ini file somehow....

--Q.