Connecting to MS SQL Server

[monona]

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.

[Reeza]

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.

---

[monona]

Where can I obtain dsnName? Where can I access?

[Reeza]

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?

---

[SASKiwi · Accepted solution]

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.

[ScottBass]

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

[monona]

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.

[SASKiwi]

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

[monona]

Here!

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

[SASKiwi]

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

Trusted_Connection=yes;

or

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

[monona]

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

[SASKiwi]

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

[ScottBass]

---

@monona wrote:

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

---

Congratulations @SASKiwi !!! 😄

[monona]

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?

[SASKiwi]

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.