- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Can you post your LIBNAME statement please. The connection strings are fiddly to get right. For example blanks matter.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
libname abc odbc noprompt = "server=XXX;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = XXX schema = dbo;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OMG!!!! I LOVE YOU. IT WORKS!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.