topic Re: SQL Server 2012 Remote connection through SAS ODBC in SAS Programming

SQL Server 2012 Remote connection through SAS ODBC

mlogan — Thu, 05 May 2016 23:48:55 GMT

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

Re: SQL Server 2012 Remote connection through SAS ODBC

SASKiwi — Fri, 06 May 2016 08:08:51 GMT

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.

Re: SQL Server 2012 Remote connection through SAS ODBC

mlogan — Fri, 06 May 2016 15:57:53 GMT

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,

Re: SQL Server 2012 Remote connection through SAS ODBC

SASKiwi — Sun, 08 May 2016 00:08:20 GMT

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;

Re: SQL Server 2012 Remote connection through SAS ODBC

mlogan — Sun, 08 May 2016 02:17:03 GMT

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?

Re: SQL Server 2012 Remote connection through SAS ODBC

SASKiwi — Sun, 08 May 2016 19:52:24 GMT

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?

Re: SQL Server 2012 Remote connection through SAS ODBC

mlogan — Tue, 10 May 2016 00:15:00 GMT

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.

Re: SQL Server 2012 Remote connection through SAS ODBC

mlogan — Tue, 10 May 2016 00:21:39 GMT

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;