Odbc Windows 7

[Giorgio Aguilar]

Ingeneral, I've loved the connections tab that was implemented within RStudio. However, I'm now trying to connect to my employer's Microsoft SQL Database and is normally accessed through Windows Authentication.

I'm on a Mac. My data is in SQL Server. Windows VDI is super slow. I want to run queries against the SQL server out of RStudio. I feel like it may be more efficient than going into the virtual Windows, firing up SSMS, running a query, saving the results on a shared drive, and then picking the data up from Mac.

That's my current workflowfor accessing anything on SQL Server. I can access Redshift (postgres driver) from Mac, but I have not figured out how to get the combination of ODBC windows auth working right in Mac at all.

For my use case that's fine as I have two work arounds: We have an internal system where I can turn any query into an API that I pass parameters to. Because it's just a GET request that works from any platform. So sometimes after I work out a query I just turn it into an API. Clearly that's not helpful for stuff like dbplyr backends. For interactive dplyr stuff I just say in Win. The other workaround is to cache data locally that I fetched from Windows.

At the end of the day, you'll need to use either "open text" credentials, or somehow tack into a Kerberos ticket to take advantage to the Integrated Security from MS. Internally, we are able to test Mac with MS databases by using a service account.

Would that authenticate me based on Windows authentication while I'm on Mac? I don't think so.

I was bringing up examples of software that is able to log you into the MS SQL Server using Windows Authentication, while on Mac.

A System DSN by definition applies to the entire computer irrespective of which logon account is used, so (if I'm reading your question right) that means that (1) the answer is "yes", and (2) you will need Admin rights to create the DSN.

Have you looked as DSN-less connections, by the way? I believe that they would be much more appropriate for your requirement, and would also remove the need for client configuration before your app could be used.

You can also use the "runas" command to launch the ODBC Data Source Administrator under your admin account while logged under your normal account. That would enable you to configure and test the connection without receiving a "user is not associated with a trusted SQL Connection" error.

As Jimmy said, the DSN definition would not be tied to the admin account, but would use whatever Windows Authentication account you were logged in as when using the connection later. (So you would need to use "runas" again to launch any programs that used the connection, unless you were logged in under your admin account.)

I'm going to add to Mac's answer that yes, this definitely works to set up the ODBC connection and it does work for a while. Probably for as long as the Kerberos authentication stays active. Unfortunately, this isn't the permanent authentication I would prefer for a System DSN. Here is the batch file that I use to launch odbcad32.exe:

I don't believe there is the ability to do this. Using a trusted NT connection to SQL Server implies that there is no password sent while authenticating to the server and that the existing NT token is used to authenticate. In other words, SQL Server "trusts" the NT authentication. It will use whatever user is logged in at the time of connection.

I have found that using the Windows Credential Manager works quite well. You can add the windows credential directly. The trick is you need to have the fully qualified domain name including the port, as well as your complete Active Directory username with domain qualifier, so something like mydb.myinternaldomain.com:1433 and the myinternaldomain\myusername with your password. Then you can add mydb.myinternaldomain.com as an ODBC source and windows will magically swap in the right credentials. This also works for Sql Server Management Studio.

Devart ODBC Drivers are powerful connectors for a host of database management systems and cloud services that allow you to connect to your data from virtually any third-party application or programming language that supports the ODBC API. By a third-party application, we mean tools like Power BI, Tableau, Microsoft Excel, etc. See this page for the list of external applications that have been tested for compatibility with our drivers.

6. Input your activation key or choose Trial if you want to evaluate the product before getting a license. You can load the activation key by clicking Load Activation Key and selecting the license file from your machine. Click Next and then Install.

Before connecting a third-party application to a database or cloud source through ODBC, you need to set up a data source name (DSN) for the ODBC driver in the Data Source Administrator. A 64-bit version of the Microsoft Windows operating system includes both the 64-bit and 32-bit versions of the Open Database Connectivity (ODBC) Data Source Administrator tool (odbcad32.exe):

4. Enter a name for your data source in the corresponding field. Fill in the parameters for the ODBC connection string, which is driver-specific. In most of our ODBC drivers for databases, a connection string with basic parameters requires the user to only input their server address, port number, and login credentials, since Devart ODBC drivers allow direct access to the database without involving additional client libraries.

5. Click Test Connection to verify connectivity. If you see the Connection Successful message, click OK to save the DSN. You should now see your new DSN in the User DSN tab of the ODBC Data Source Administrator tool.

You can use an ODBC connection to connect to your Amazon Redshift cluster from many third-party SQL client tools and applications. To do this, set up the connection on your client computer or Amazon EC2 instance. If your client tool supports JDBC, you might choose to use that type of connection rather than ODBC due to the ease of configuration that JDBC provides. However, if your client tool doesn't support JDBC, follow the steps in this section to configure an ODBC connection.

Amazon Redshift provides 64-bit ODBC drivers for Linux, Windows, and macOS X operating systems. The 32-bit ODBC drivers are discontinued. Further updates will not be released, except for urgent security patches.

You install the Amazon Redshift ODBC driver on client computers accessing an Amazon Redshift data warehouse. Each computer where you install the driver must meet a list of minimum system requirements. For information about minimum system requirements, see the Amazon Redshift ODBC connector installation and configuration guide.

Use the following procedure to download the Amazon Redshift ODBC drivers for Windows operating systems. Only use a driver other than these if you're running a third-party application that is certified for use with Amazon Redshift and that requires a specific driver.

After you download and install the ODBC driver, add a data source name (DSN) entry to the client computer or Amazon EC2 instance. SQL client tools use this data source to connect to the Amazon Redshift database.

We recommend that you create a system DSN instead of a user DSN. Some applications load the data using a different user account. These applications might not be able to detect user DSNs that are created under another user account.

For authentication using AWS Identity and Access Management (IAM) credentials or identity provider (IdP) credentials, additional steps are required. For more information, see Configure a JDBC or ODBC connection to use IAM credentials.

Enter a name for the data source. You can use any name that you want to identify the data source later when you create the connection to the cluster. For example, if you followed the Amazon Redshift Getting Started Guide, you might type exampleclusterdsn to make it easy to remember the cluster that you associate with this DSN.

Specify the endpoint for your Amazon Redshift cluster. You can find this information in the Amazon Redshift console on the cluster's details page. For more information, see Configuring connections in Amazon Redshift.

Enter the name of the Amazon Redshift database. If you launched your cluster without specifying a database name, enter dev. Otherwise, use the name that you chose during the launch process. If you followed the Amazon Redshift Getting Started Guide, enter dev.

Under Authentication, specify the configuration options to configure standard or IAM authentication. For information about authentication options, see "Configuring Authentication on Windows" in Amazon Redshift ODBC Connector Installation and Configuration Guide.

Choose a mode for handling Secure Sockets Layer (SSL). In a test environment, you might use prefer. However, for production environments and when secure data exchange is required, use verify-ca or verify-full. For more information about using SSL on Windows, see "Configuring SSL Verification on Windows" in Amazon Redshift ODBC Connector Installation and Configuration Guide.

Under Additional Options, specify options on how to return query results to your SQL client tool or application. For more information, see "Configuring Additional Options on Windows" in Amazon Redshift ODBC Connector Installation and Configuration Guide.

In Logging Options, specify values for the logging option. For more information, see "Configuring Logging Options on Windows" in Amazon Redshift ODBC Connector Installation and Configuration Guide.

Under Data Type Options, specify values for data types. For more information, see "Configuring Data Type Options on Windows" in Amazon Redshift ODBC Connector Installation and Configuration Guide.

Configure TCP keepalives on Windows to prevent connections from timing out. For information about how to configure TCP keepalives on Windows, see Amazon Redshift ODBC Connector Installation and Configuration Guide.

3a8082e126