Architecting, installing and maintaining your SAS environment

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

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


Accepted Solutions
Solution
‎05-18-2017 01:02 PM
Occasional Contributor
Posts: 19

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

[ Edited ]

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

 

thanks again for all of your help.

View solution in original post


All Replies
Contributor
Posts: 74

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

Hi @imaahm

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!

Occasional Contributor
Posts: 19

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

[ Edited ]

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

Super User
Posts: 3,256

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

In addition to @Anand_V'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.

Occasional Contributor
Posts: 19

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

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

Super User
Posts: 3,256

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

[ Edited ]

@imaahm - 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...

SAS Super FREQ
Posts: 299

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

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

Occasional Contributor
Posts: 19

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

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

Super User
Posts: 3,256

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

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

 

screenshot13.jpg

Solution
‎05-18-2017 01:02 PM
Occasional Contributor
Posts: 19

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

[ Edited ]

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

 

thanks again for all of your help.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 2729 views
  • 2 likes
  • 4 in conversation