topic Connecting to MS SQL Server in SAS Programming

Connecting to MS SQL Server

monona — Wed, 20 Feb 2019 20:21:45 GMT

I am trying to connect to MS SQL server 2014. I found that I need odbc connection. So I tried to set this up, but faced this kind of problem.

My windows account is entirely authorized for the database at the moment, but still not working.

I want to figure out how to resolve this.
I would glad to hear if there is another way to connect SAS to MS sql server.

Re: Connecting to MS SQL Server

Reeza — Wed, 20 Feb 2019 20:46:00 GMT

That's not a SAS issue, that's something you need to talk to your IT team. Once you have the DSN set up on your computer, then getting access within SAS is relatively easy.

libname myData odbc dsn=dsnName schema=dbo;

Perhaps your DB doesn't allow for single sign on, did you try signing in manually?

@monona wrote:

I am trying to connect to MS SQL server 2014. I found that I need odbc connection. So I tried to set this up, but faced this kind of problem.

My windows account is entirely authorized for the database at the moment, but still not working.

I want to figure out how to resolve this.

I would glad to hear if there is another way to connect SAS to MS sql server.

Re: Connecting to MS SQL Server

monona — Wed, 20 Feb 2019 20:48:38 GMT

Where can I obtain dsnName? Where can I access?

Re: Connecting to MS SQL Server

Reeza — Wed, 20 Feb 2019 21:00:17 GMT

When you set up the DSN, in the screenshot shown, you set the DSN name there.

You have to fix that error you're showing first, however, that's not something the forum can help you out with, its dependent on your set up and permissions which you really shouldn't share with us.

@monona wrote:
Where can I obtain dsnName? Where can I access?

Re: Connecting to MS SQL Server

SASKiwi — Thu, 21 Feb 2019 02:09:52 GMT

If you prefer not doing DSNs you can do the complete connection string in SAS like so:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

This is what we do as it avoids having to maintain DSNs entirely.

Re: Connecting to MS SQL Server

ScottBass — Thu, 21 Feb 2019 02:34:52 GMT

Further to @SASKiwi 's comment, I have written a macro to assist me at my client since I work with SQL Server all the time.

Perhaps you may find this useful, or at least can use it as a starting point. Obviously you may need to edit it for your site specific requirements.

https://github.com/scottbass/SAS/blob/master/Macro/libname_sqlsvr.sas

Re: Connecting to MS SQL Server

monona — Thu, 21 Feb 2019 02:36:36 GMT

Thanks for your reply! I got error messages though.

ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for
user ''. : [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute
ERROR: Error in the LIBNAME statement.

Re: Connecting to MS SQL Server

SASKiwi — Thu, 21 Feb 2019 02:39:54 GMT

Can you post your LIBNAME statement please. The connection strings are fiddly to get right. For example blanks matter.

Re: Connecting to MS SQL Server

ScottBass — Thu, 21 Feb 2019 02:43:20 GMT

@monona wrote:
Thanks for your reply! I got error messages though.

ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for
user ''. : [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute
ERROR: Error in the LIBNAME statement.

Does your SQL Server support trusted connections, i.e. your already authenticated network identity (when you logged in) if sufficient to grant access to SQL Server?

Do you use SQL Server Management Console, or some other tool, to connect to SQL Server outside of SAS?

Before posting more questions, you may want to invest some time here:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm

If you haven't yet read this, you should...

Re: Connecting to MS SQL Server

monona — Thu, 21 Feb 2019 02:44:17 GMT

Here!

libname abc odbc noprompt = "server=XXX;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = XXX schema = dbo;

Re: Connecting to MS SQL Server

monona — Thu, 21 Feb 2019 02:48:11 GMT

Yes! I log in with my windows authentication when loggin in ms sql server management studio.

Re: Connecting to MS SQL Server

SASKiwi — Thu, 21 Feb 2019 02:57:03 GMT

Sorry, my bad. Try this (added underscore):

Trusted_Connection=yes;

libname abc odbc noprompt = "server=XXX;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes" DATABASE = XXX schema = dbo;

Re: Connecting to MS SQL Server

monona — Thu, 21 Feb 2019 02:56:14 GMT

OMG!!!! I LOVE YOU. IT WORKS!!!

Re: Connecting to MS SQL Server

monona — Thu, 21 Feb 2019 02:59:10 GMT

One last question....!
I actually create the library like that, but it doesn't show up in the database on ms sql server studio. do you know where the library i create on sas is stored?

Re: Connecting to MS SQL Server

SASKiwi — Thu, 21 Feb 2019 03:01:23 GMT

@monona -I'm pleased you like this solution. I reckon it's pretty cool too yet it is not a well known way of connecting to relational databases.

Re: Connecting to MS SQL Server

SASKiwi — Thu, 21 Feb 2019 03:04:27 GMT

It won't show up there, it is purely a SAS thing. It will appear in your SAS Enterprise Guide server list or the library list in SAS Studio or the SAS Windowing Environment.

Re: Connecting to MS SQL Server

monona — Thu, 21 Feb 2019 03:06:11 GMT

So is it another story to push sas data set to sql server directly? sorry for many questions...

Re: Connecting to MS SQL Server

ScottBass — Thu, 21 Feb 2019 03:09:13 GMT

@monona wrote:
OMG!!!! I LOVE YOU. IT WORKS!!!

Congratulations @SASKiwi !!! 😄

Re: Connecting to MS SQL Server

SASKiwi — Thu, 21 Feb 2019 03:23:21 GMT

The same LIBNAME will work for updating SQL Server as well as reading, assuming you have the right database privileges.

Re: Connecting to MS SQL Server

tampham92 — Mon, 02 Aug 2021 16:40:44 GMT

Hello, I am trying to connect to MS SQL Server data table through SAS and I ran into this issue and not sure what it means.

I ran the following code and got issue

proc sql;
connect to SQLSVR as mydb (datasrc="SQL_SAS" user="XXX" password="XXX");
select * from connection to mydb
(select * from DATA_TABLE);
quit;
run;

23 GOPTIONS ACCESSIBLE;

24 proc sql;
25 connect to SQLSVR as mydb (datasrc="SQL_SAS" user="D113338" password=XXXXXXXXXX);
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
26 select * from connection to mydb
27 (select * from DATA_TABLE);
ERROR: The MYDB engine cannot be found.
ERROR: A Connection to the mydb DBMS is not currently supported, or is not installed at your site.
28 quit;

I already added that SQL_SAS datasource into the ODBC Datasouce Administrator but it still did not work.

Thank you so much for any help.