Administer ODBC data sources
About ODBC data sources. Add an ODBC data source. About ODBC data sources. A data source is a source of data combined with the connection information that is required to access that data. Examples of data sources are SQL Server, Oracle RDBMS, a spreadsheet, and a text file. This simple how-to article will explain how to setup an ODBC (Open Database Connectivity) Connection to a SQL Server Database. This connection is most commonly used for a client-server application. For example, if you have a SQL Server Database as a backend and a front-end using something like MS Access.
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search. XX' I have created a user account in the MySQL Administrator and added a host to enable remote access, I have connectioj made an exception for my Windows Firewall on port but the connection still connecyion.
The error means that you are not allowed to access the web server remotely. Please, see Solution to Connecting remotely when you get Error post for details. First of all, it's a very bad idea to have a worldwide open port to your database server. Especially orbc it's on the MySQL default port.
You're just inviting people to try to break in to your database. Have you tried using a port scanning tool like nmap on your hlw your remote computer to odhc the database server? But that's not a good idea. If you are going to access the database from another remote server, why don't you place a file inside your site with some security such htpasswd protection or some authentication odgc If you are going to get data of a mysql server to another server, be precise with the requirement This step is needed to be able to connect from the computer After following some of the suggestions in the answers found here, I decided to check the firewall rules on the Win server hosting MySQL.
I had an inbound rule set up that locked port down to be accessible only from a list of IP addresses. Once I added my latest server's IP to the list, I was able to connect. Sign up to join this community. Go best answers are voted up and rise to the top.
Stack Overflow how to add odbc connection Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
Learn more. Asked 10 years, 11 connevtion ago. Active 3 years, 11 months ago. Viewed k times. XX' I have created a user account in the MySQL Administrator and added a host to enable remote access, I have also made an exception for my Windows Firewall on port but the connection still fails.
What is the problem? How to fix a fiberglass boat this question. Connectiob a programming related question, but anyway, check if the remote computer is able to connect to any other service running on the same computer you're running the MySQL server. I doubt it is however. Very likely to be a NAT problem.
Add a comment. Active Oldest Votes. I suppose your mysql server is running on Windows Improve this answer. TallTed 2 2 silver badges 9 9 bronze badges. Maybe this seems a stupid question but Which is the result of netstat? May 3 '10 connectjon The rest of the parameters of your connection are the same. Alexander Alexander 4 4 bronze badges. As amphetamachine describes in the answer above, this is far from best practices.
If it's just for data synchronization, go for db server concepts Vijay Vijay 4 4 bronze badges. MatsT MatsT 3 3 bronze badges. Permission gives a different error code. Andrew Schulman 8, 20 20 gold badges 28 28 silver badges 45 45 bronze badges.
Accept all how to get your password Customize settings.
Enter the connection string for the SQL DB, and then page through the wizard to establish high availability. Add the new RD Connection Broker to the deployment In Server Manager, click Remote Desktop Services > Overview. Right-click the RD Connection Broker, and then click Add RD Connection Broker Server. Dec 11, · The majority of ODBC based extensions will use this as their primary extensibility function. The function accepts two arguments—a connection string, and an options record to provide behavior overrides. The wooustoday.com function allows you to execute SQL statements through an ODBC driver. It acts as a passthrough for query execution. When I am trying to connect to a MySQL database from a remote computer I get a prompt saying: Connection Failed: [HY] [MySQL][ODBC Driver]Can't Connect to MySQL server on 'wooustoday.com' () I have created a user account in the MySQL Administrator and added a host to enable remote access, I have also made an exception for my Windows Firewall on port but the connection .
Using M's built-in Odbc. Wrapping the Odbc. DataSource function will allow your connector to inherit default query folding behavior based on the capabilities reported by your driver. This will enable the M engine to generate SQL statements based on filters and other transformations defined by the user within the Power Query experience, without having to provide this logic within the connector itself.
ODBC extensions can optionally enable Direct Query mode, allowing Power BI to dynamically generate queries at runtime without pre-caching the user's data model. Enabling Direct Query support raises the difficulty and complexity level of your connector. When Direct Query is enabled, Power BI will prevent the M engine from compensating for operations that cannot be fully pushed to the underlying data source. This section builds on the concepts presented in the M Extensibility Reference, and assumes familiarity with the creation of a basic Data Connector.
Additional samples can be found in the ODBC samples directory. DataSource , and Odbc. The Odbc. DataSource function provides a default navigation table with all databases, tables, and views from your system, supports query folding, and allows for a range of customization options.
The majority of ODBC based extensions will use this as their primary extensibility function. The function accepts two arguments—a connection string, and an options record to provide behavior overrides. It acts as a passthrough for query execution. Unlike the Odbc. DataSource function, it doesn't provide query folding functionality, and requires that SQL queries be provided by the connector or end user. When building a custom connector, this function is typically used internally to run queries to retrieve metadata that might not be exposed through regular ODBC channels.
The function accepts two arguments—a connection string, and a SQL query. Custom connectors can accept any number of function arguments, but to remain consistent with the built-in data source functions shipped with Power Query, the following guidelines are recommended:. Require the minimal set of parameters used to establish a connection to your server. The less parameters end users need to provide, the easier your connector will be to use. Although you can define parameters with a fixed number of values that is, a dropdown list in the UI , parameters are entered before the user is authenticated.
Any values that can be discovered programmatically after the user is authenticated such as catalog or database name should be selectable through the Navigator. The default behavior for the Odbc. DataSource function will be to return a hierarchical navigation table consisting of Catalog Database , Schema, and Table names, although this can be overridden within your connector.
If you feel your users will typically know what values to enter for items they would select from the Navigator such as the database name , make these parameters optional. Parameters that can be discovered programmatically should not be made required. The last parameter for your function should be an optional record called "options". This parameter typically allows advanced users to set common ODBC related properties such as CommandTimeout , set behavior overrides specific to your connector, and allows for future extensibility without impacting backwards compatibility for your function.
Credential related parameters should be specified as part of the connector's supported Authentication methods. By default, all required parameters for your data source function are factored into the Data Source Path value used to identify user credentials. Note that while the UI for the built-in Odbc. DataSource function provides a dropdown that allows the user to select a DSN, this functionality is not available through extensibility.
If your data source configuration is complex enough to require a fully customizable configuration dialog, it's recommended you require your end users to pre-configure a system DSN, and have your function take in the DSN name as a text field.
DataSource function takes two parameters—a connectionString for your driver, and an options record that lets you override various driver behaviors. Through the options record you can override capabilities and other information reported by the driver, control the navigator behavior, and affect the SQL queries generated by the M engine.
A duration value that controls how long the server-side query is allowed to run before it's cancelled. A duration value that controls how long to wait before abandoning an attempt to make a connection to the server. A logical value that sets whether to generate navigation properties on the returned tables. If calculating foreign key dependencies is an expensive operation for your driver, you may want to set this value to false.
A logical value that sets whether to view the tables grouped by their schema names. When set to false, tables will be displayed in a flat list under each database. If your driver supports Windows Authentication, but requires additional or alternative settings in your connection string, you should set this value to false and use the CredentialConnectionString option record field described below.
The following table describes the options record fields that are only available through extensibility. Fields that aren't simple literal values are described in subsequent sections. A record containing one or more overrides to control SQL query generation. A logical value that enables client-side connection pooling for the ODBC driver. Most drivers will want to set this value to true. A logical value that controls whether your connector allows native SQL statements to be passed in by a query using the Value.
NativeQuery function. Note: this functionality is currently not exposed in the Power Query user experience. Users would need to manually edit their queries to take advantage of this capability. A table value containing implicit type conversions supported by your driver or backend server. Values in this table are additive to the conversions reported by the driver itself.
This field is typically used in conjunction with the SQLGetTypeInfo field when overriding data type information reported by the driver.
Common uses of this function include handling SSL connection failures, providing a download link if your driver isn't found on the system, and reporting authentication errors.
A record providing various overrides of driver capabilities, and a way to specify capabilities that aren't expressed through ODBC 3. When the value is set to a table, the value completely replaces the type information reported by the driver. When the value is set to a function, your function will receive the result of the original call to SQLGetTypeInfo, allowing you to modify the table. Allows conversion of numeric and text types to larger types if an operation would cause the value to fall out of range of the original type.
For example, when adding Int Max, the engine will cast the result to Int64 when this setting is set to true. This is generally only set by connectors created by Microsoft that ship with Power Query.
The AstVisitor field is set through the Odbc. DataSource options record. It's used to modify SQL statements generated for specific query scenarios. Providing an override for this value has been deprecated and may be removed from future implementations. This field is a function that receives two Int Type arguments skip, take , and returns a record with two text fields Text, Location. If an offset is not specified, the skip value will be null. The Location field specifies where to insert the clause.
The following table describes supported values. A number value ranging from 1 to 7 that indicates the number of decimal places supported for millisecond values. This value should be set by connectors that want to enable query folding over datetime values. A logical value that indicates that statements should be prepared using SQLPrepare. A logical value that indicates the driver supports the TOP clause to limit the number of returned rows.
A list of text values that specify the character s to use when escaping string literals and LIKE expressions. For all other conformance levels, this value defaults to false. A logical value that indicates whether the generated SQL should include numeric literals values. When set to false, numeric values will always be specified using Parameter Binding. A logical value that indicates whether the generated SQL should include string literals values.
When set to false, string values will always be specified using Parameter Binding. A logical value that indicates whether the generated SQL should include date literals values.
When set to false, date values will always be specified using Parameter Binding. A logical value that indicates whether the generated SQL should include time literals values. When set to false, time values will always be specified using Parameter Binding. A logical value that indicates whether the generated SQL should include timestamp literals values. When set to false, timestamp values will always be specified using Parameter Binding.
The source parameter contains a table with the data type information. Numeric constants for each of these fields can be found in the ODBC specification. The full list of InfoTypes that are checked can be found in the Mashup Engine trace files. Note that in Power Query scenarios, the connector will be used in a Read Only mode. A integer value that specifies the relationship between the columns in the GROUP BY clause and the non-aggregated columns in the select list:.
It cannot contain any other columns. It can contain columns that are not in the select list. The meaning of non-grouped, non-aggregated columns in the select list is data source—dependent. The following helper function can be used to create bitmask values from a list of integer values:.
The first approach is used to completely override the values returned by the ODBC driver.