DATA Step, Macro, Functions and more

SQL Server 2012 Remote connection through SAS ODBC

Reply
Regular Contributor
Posts: 215

SQL Server 2012 Remote connection through SAS ODBC

[ Edited ]

Can someone tell me the code to connect to SQL Server remotely through SAS ODBC so that I can see the schema as well?

I have the IP Addesss, User ID and Password.

 

Do I also have to set up something at Control Panel\All Control Panel Items\Administrative Tools\Data Source (ODBC)?

 

Thanks,

 

Mike

Super User
Posts: 3,100

Re: SQL Server 2012 Remote connection through SAS ODBC

Are you using SAS on your PC to connect to SQL Server or are you using a remote SAS Server? In either case if SAS is running on Windows you can avoid the Control Panel setup by specifying everything in a database connection string.

 

Is your database userid and password your Windows user account and password or are they different? The reason I ask is its very common to connect to SQL Server using Windows Authentication (your connection is verified using your Windows user account).

 

The one thing you are missing at the moment is you need the name of a database to connect to.

 

Then you can construct a test LIBNAME like this (please note syntax may not be quite right as I'm doing this from memory):

 

libname SQLServr noprompt = 'server=123.456.789;user=MyUser;password=MyPassword' database = MyDatabase schema = dbo;

 Once you've confirmed the above questions I can give you a more accurate answer.

Regular Contributor
Posts: 215

Re: SQL Server 2012 Remote connection through SAS ODBC

Hi SASKiwi,
I am using SAS on my PC. Server UserID and Password is different than my windows.

I used your code, it went through successfully, but I don't see any library name on the left though. Can you please elaborate a bit more so that I can extract data from here. Thanks,
Super User
Posts: 3,100

Re: SQL Server 2012 Remote connection through SAS ODBC

[ Edited ]

OK, since you are connecting from your PC that means you can specify everything to connect to SQL Server in a connection string. Also I forgot to add the DRIVER option in my previous post so it is not surprising it is not working for you. I can check this better at work but that won't be until tomorrow.

 

If the LIBNAME works OK then the library name should appear in the library list and expanding it should show tables. 

 

 

libname SQLServr noprompt = 'DRIVER=SQL Server;server=123.456.789;user=MyUser;password=MyPassword' database = MyDatabase schema = dbo;
Regular Contributor
Posts: 215

Re: SQL Server 2012 Remote connection through SAS ODBC

Hi SASKiwi,
My SQL Server is sitting in a virtual machine. If it is that complicated to connect remotely, I can always install SQL Server on my local pc (where SAS is installed). Do you think that will be a better solution for me?
Super User
Posts: 3,100

Re: SQL Server 2012 Remote connection through SAS ODBC

In theory it should be just the same so I suggest you persevere with your current setup. Also I didn't quite get the LIBNAME right (should specify ODBC engine):

 

libname SQLServr odbc noprompt = 'DRIVER=SQL Server;server=123.456.789;user=MyUser;password=MyPassword' database = MyDatabase schema = dbo;

Does this help? Does the LIBNAME assign and appear in your library window?

 

Regular Contributor
Posts: 215

Re: SQL Server 2012 Remote connection through SAS ODBC

Hi SASKiwi,
I tried the code and I get the following error.

ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL
Server does not exist or access denied. : [Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen (Connect()). : [Microsoft][ODBC SQL Server Driver]Invalid
connection string attribute
ERROR: Error in the LIBNAME statement.
Regular Contributor
Posts: 215

Re: SQL Server 2012 Remote connection through SAS ODBC

Admin & abcd1234 is my userid and password for virtual mechine log in. Once I am in virtual machine I hit the Sql ser management studio and i could log in to sql server with windows authentication. but its a problem to log in from sas with the following code:

libname SQLServr
odbc noprompt = 'DRIVER=SQL Server;
server=101.102.10.11;
user=Myserver\admin;
password=abcd1234'
database = Mydatabase
schema = dbo;
Ask a Question
Discussion stats
  • 7 replies
  • 413 views
  • 0 likes
  • 2 in conversation