SQL Server 2012 Remote connection through SAS ODBC

[mlogan]

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

[SASKiwi]

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.

[mlogan]

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,

[SASKiwi]

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;

[mlogan]

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?

[SASKiwi]

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?

[mlogan]

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.

[mlogan]

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;