Steps to connect to SQL Server and load data into LASR table

[L2Fly]

Hello - I am looking for a document or help on how to setup a connection from SAS 9.4 on Windows Server to SQL Server database. I have gone to the SAS server and created an ODBC data source connection under System DSN tab to our sql server and connection is a success.

I am assuming I should create a Library under Data Library Manager (SAS Management Console) and then import my tables in that library. I would appreciated any help or document on this task.

Best

[AnandVyas]

Hi @L2Fly

This Blog Post is best for this question:

http://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/

If you wish your auth domain to be available in drop down under Register the DBMS server, screen 5
you can create that before starting to register the new server under user manager. Follow this link for the same.

http://support.sas.com/documentation/cdl/en/mcsecug/64770/HTML/default/viewer.htm#n11xfvoau0jli0n1cl...

Hope this helps.

Anand!

[L2Fly]

Hi Anand_V, the link to the blog was great.  I was able to go through it and it helped very much.  Now when I right click and select Display LIBNAME Statement I get the following but the problem is that it is using my userID for user.  What I want to do is use a service account I have created to be used for user=myserviceacct

LIBNAME LibMyLibName ODBC  DATAsrc=SASSQL  SCHEMA=DBO  USER=MyID  PASSWORD="{???}123456789012345678890" ;

I logged into the SAS Management console as sasadm and when I run the above statement it passes my userID but I want to use the fix service account.

Best and thanks

[SASKiwi]

In addition to @AnandVyas's very helpful post you may wish to consider not using DSNs at all and instead define everything in a connection string. This post shows you how:

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

To define a connection string in SMC, just choose the CONNECTION STRING option instead of the DSN option when defining the ODBC server.

Also a good alternative to registering your tables in metadata is to add the an extra attribute to your LIBRARY definition - AssignMode = 2. This will ensure your tables get listed in EG without registering.

[L2Fly]

Thank you Anand-V and SASKiwi for your replies.  SASKiwi -  SAS did our installation and it looks like they used DSN for connection not sure why.  Also I looked at the link you provided and if I do it that way then I need to place the username and password into the string and I am not sure who will be able to see that!  I forgot to mention in my first but yes I want to register the tables in MetaData first but my issue is finding the steps to do it.  I have looked and searched but they are mostly talking about importing data from Excel...

Here what I think I should be doing (if using the DSN).

1- I logged in into the server as a server admin user (who has access to the SQL Server) and created the ODBC connection in DSN.

2- Now what I need to do is to register tables into metadata (I am assuming when I do this it will create the library with the list of tables in it)

3- Load the data into LASR

Stpes two and three and perhaps for all of the above steps I need some guide and help.

Again thanks so much to both of you for your help

Regards

[SASKiwi]

@L2Fly - you don't have to put your userid and password in the connection string. If you are using Windows Authentication to connect to SQL Server you can use this string:

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

The - Trusted Connection=yes - part of the string says use my Windows user account and password to authenticate with SQL Server.

I also suggest trying out the server connection in a SAS LIBNAME statement first as it is a lot easier to test:

libname test ODBC noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

@anja's link also will lead you to examples of using connection strings:

http://support.sas.com/documentation/cdl/en/bidsag/69848/HTML/default/viewer.htm#p1t9q2knb9tq5ln101d...

[anja]

Hi

take a look at the following doc:

http://support.sas.com/documentation/cdl/en/bidsag/69848/HTML/default/viewer.htm#n017taop8cx07in1r3a...

It will "land" on the Ora example. Look to the left and you'll find a list of all DBMS etc libs you can create in

SASMC.

Hope that helps

Thanks

Anja

[L2Fly]

Hello everyone and thanks for your help and posts.  After reading most of the links I was able to do the following.  I would love and very much appreciated to know your thoughts and help on this: 

Steps I took:

• Created a domain service account called SASSQLCONNECT. This service account has access to SQL Server.
• Logged in into the SAS OA server with this user (only once to setup the ODBC driver) and did setup ODBC connection to SQL Server.
• I followed this blog http://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/ and as the result created an entry under [Server Manager] called [SQL Server: SQLDevDB] and also a Library under [Data Library Manager] called [SQL Server: SQLDevDB Library].

Here are my question:

• I go and select and right click on [SQL Server: SQLDevDB Library] in Libraries and select Register Tables > Next > there is a log on windows pops up for SASAppOA, What user ID I need to log-in with? (at this time I just login with my own userID) and after I do this I get another log-on windows for [SQL Server: SQLDevDB Library] and again I use my own login (I am sure this is not correct)
• Then I see the list of my tables from database, I select one and Location is set to /Shared Data > Next> Finish
• I am assuming now I have registered the table in MetaData server.
• Now I want to load the data into LASR table, how should I do that?
• I can go and open SAS Enterprise Guide and under Servers SASAppOA and Explore the data.
• I use SAS Enterprise Guide to Upload to LASR by finding the registered table but I get an error on a popup window for Upload to LASR saying “Unable to locate any LASR libraries. This task requires a LASR library”

Again thank you for your feedback

Regads

[SASKiwi]

If you would like to bypass registering tables but still have them available, add this to your library definition:

[L2Fly · Accepted solution]

Thank you but this did not help me.  I will contact SAS Support.  

thanks again for all of your help.