When running a third party developed EXE located on the shared folder of Windows Server 2012 R2, which connects to SQL Server 2012 Express, the following error occurs:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
When the EXE is ‘Run as Administrator’ it works fine. But this application interfaces with Office and other programs, so running as administrator puts it into protected memory space that conflicts with other applications. I need it to open normally, as before.
This error has only occurred since client workstations had Windows 10 1803 and higher installed. Bizarrely, workstations that updated to 1803 and higher are unaffected. Only on new installs has the problem occurred.
There was something about 1803 which changed the network stack, client side, that caused a lot of different problems for SQL connections and I have seen a hundred different solutions but none work in our case.
Client machines connect to Server 2012 using domain login accounts. Each domain user account is given local admin rights and full administrator rights. The client workstations are normal Windows 10 Pro install with ESET antivirus. When testing the ODBC connection, it is successful and works. Only when running the program does the error occur.
On the Server side I have…. Enabled Named Pipes, Disabled and Removed SMB1.0
So what is going on here? Why from 1803 on must we run as administrator to get a connection?
- Remove From My Forums
-
Question
-
I have an excel (2003) macro that connects to a DB2 connection via ODBC:
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
Set db = New Connectiondb.CursorLocation = adUseClient
db.Properties(«Prompt») = adPromptCompleteRequired
db.CommandTimeout = 240
db.ConnectionTimeout = 240
db.Open Range(«BASE2»)Set rs = New Recordset
rs.Open sql, dbBut when I execute this code I keep getting an error message: [Microsoft] [ODBC Driver Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed.
I am running a Windows 7 (64 bits), IBM DB2 (v9). I have also configured the system DNS to use a 32 bits driver (in order to avoid the architecture mismatch error). And I have added my USER to the SQLLIB folder permission with full control option.
Even with all this things set, I keep getting this error message. Anyone know how to solve this problem? I am out of options here.
Thanks in advance.
Answers
-
I don’t know what is being used as the connection string. the website below says not to use ODBC (has known bug) but instead use oldb. You are setting a new connection which is using a default connection string. You can see what the connection
string is by adding a watch for the variable db and looking at the connection property.http://community.discountasp.net/showthread.php?t=7014
You also have two connection string (see below)
Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
Set db = New Connection ****** Remove this lineIf deleting the one line doesn’t work then I would changing the open statement.
From : db.Open ConnectionString:=Range(«BASE2»)
To : db.Open ConnectionString:=»Provider=SQLOLEDB;Data Source=» & Range(«BASE2»)
Or this
To : db.Open ConnectionString:=»Provider=SQLOLEDB;Data Source=Range(«»BASE2″»)»
jdweng
-
Marked as answer by
Monday, November 7, 2011 9:04 AM
-
Marked as answer by
Пересказ статьи Rayis Imayev. Error [IM002] [Microsoft][ODBC Driver Manager] «Data source name not found and no default driver specified» and who do you trust?
Это очень короткий пост, просто чтобы напомнить себе, но, если вы когда-либо пытались подключиться к базе данных PostgreSQL с помощью интерфейса ODBC (знаю, что это уже звучит, как очень интересный вызов), то могли столкнуться с таким сообщением об ошибке: “ERROR [IM002] [Microsoft][ODBC Driver Manager] Источник данных не найден и не указан драйвер по умолчанию.”
Я допускаю, что мой поиск онлайн этой ошибки сразу показал ресурсы Microsoft и Stackoverflow, которые только смутили меня и не помогли решить проблему с подключением к ресурсу PostgreSQL через ODBC:
- https://docs.microsoft.com/en-us/answers/questions/227987/34error-im002-microsoftodbc-driver-manager-data-so.html
- https://stackoverflow.com/questions/17115632/microsoftodbc-driver-manager-data-source-name-not-found-and-no-default-drive
Microsoft в своей документации на SSIS дает очень хорошее объяснение, как подключиться к источнику данных PostgreSQL, и даже приводит пример строки подключения с драйвером PostrgreSQL ODBC.
Driver={PostgreSQL ODBC Driver(UNICODE)};Server=<сервер>;Port=<порт>;Database=<база данных>;UID=<ид пользователя>;PWD=<пароль>
Мои попытки воспользоваться этим примером не привели к успеху, я по-прежнему получаю свое сообщение об ошибке: “data source name not found”.
Смущал один момент, и я захотел его проверить. Если посмотреть на список 64- или 32-битных подключений ODBC PostgreSQL, которые я сделал, обнаружилось, что они не согласуются с тем, что я читал в технической статье Microsoft.
Мой список драйверов был другим по сравнению с этой технической статьей, и изменить имена этих драйверов, чтобы они соответствовали “PostgreSQL ODBC Driver(UNICODE)”, было не в моих силах.
Тогда я решил изменить мою строку подключения на такой формат “Driver={PostgreSQL Unicode};Server=<сервер>;Port=<порт>;Database=<база данных>;UID=<ИД пользователя>;PWD=<пароль>” в соответствии со списком драйверов, которые я имел. Я тестировал её с обоими 32- и 64-битными драйверами. И только тогда все заработало, и я смог подключиться к моему экземпляру PostgreSQL. Это не означает, что такое соглашение об именовании будет работать при всех обстоятельствах: драйвер ODBC может быть обновлен до новой версии, или другие факторы могут изменить успешный путь подключения к данным. Это изменчивый мир!
Я все время вспоминаю рекомендации Илона Маска делать требования к бизнесу менее тупыми, поскольку может найтись умный человек, который предоставит вам требования, которые вы не сможете опровергнуть.
Не поймите меня неправильно, я никого не виню. Это просто доказательство того, что доверять — это нормально, но стоит все же проверять и проверять. Еще один урок для меня и не новый. 
Автор не разрешил комментировать эту запись
This article, provides useful troubleshooting tips, for the following error which you might get, under certain circumstances, when trying to connect to SQL Server from Python, using pyodbc: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect).
Prior to sharing our tips for troubleshooting the issue, let’s discuss about the two main ways of connecting to SQL Server from Python.
Ways of Connecting to SQL Server from Python
In Python, there are 2 main ways for connecting to SQL Server, using the pyodbc module and Microsoft ODBC Driver 17 for SQL Server.
Specifying the Full Connection String in Python
The first way, is to define the full connection string in you Python code.
For example, in case you are using a username/password instead of a trusted connection, here’s a relevant example:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=user;PWD=password')
In case you are using a trusted connection, here’s another relevant example:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;Trusted_Connection=yes;')
Referencing an ODBC System DSN
The second way, is to reference an ODBC DSN.
Here’s a code example, that references and ODBC DSN:
conn = pyodbc.connect('DSN=mynewdsn;UID=user;PWD=password')
Learn more about SQL Server Data Access from Python – Enroll to our Course!
Enroll to our online course “Working with Python on Windows and SQL Server Databases” with an exclusive discount, and get started with Python data access programming for SQL Server databases, fast and easy!
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!) Enroll from $12.99
Troubleshooting the Issue
The error message we are examining in this post, indicates that we are trying to connect to SQL Server, using the second method, that is referencing an ODBC DSN and the process fails.
In this case, we can further troubleshoot the issue, by performing the below checks.
Check 1 – Verify that the DSN Name is Valid
Check in ODBC Data Source Administrator that the DSN exists with the exact same name you are referencing it in your Python code.
Additionally, you must check that the DSN indeed works.
For checking the DSN:
- 64-bit ODBC Data Source Administrator if you are using a 64-bit version of Windows:
C:WINDOWSSystem32odbcad32.exe
- 32-bit ODBC Data Source Administrator if you are using a 32-bit version of Windows:
C:WINDOWSSysWOW64odbcad32.exe
Check 2 – Verify that the DSN is Correctly Referenced in Python Code
If the DSN uses a username/password, you need to also specify it in your Python code as per below example:
conn = pyodbc.connect('DSN=dsn_name;UID=user;PWD=password')
If the DSN uses a trusted connection, you need to also specify it in your Python code as per below example:
conn = pyodbc.connect('DSN= dsn_name;Trusted_Connection=yes;')
If None of the Above Helps
If none of the above helps, then you may consider instead of using a DSN, to define the full connection in your Python code (see first way above).
Featured Online Courses:
- Working with Python on Windows and SQL Server Databases
- SQL Server 2022: What’s New – New and Enhanced Features
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
- Python Data Access Fundamentals
- How to Connect to SQL Server Databases from a Python Program
- What is Abstraction in Object Oriented Programming?
- How to Run the SQL Server BULK INSERT Command from Within a Python Program
- Useful Python Programming Tips
- Main Data Structures in Python
- IndentationError: expected an indented block in Python – How to Resolve it
- Working with Python on Windows and SQL Server Databases (Course Preview)
- How to Write to a Text File from a C++ Program
- How to Establish a Simple Connection from a C# Program to SQL Server
- The timeout period elapsed prior to obtaining a connection from the pool
- Closing a C# Application (including hidden forms)
- Changing the startup form in a C# project
- Using the C# SqlParameter Object for Writing More Secure Code
- Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
- Missing parentheses in call to ‘print’. did you mean print(…) – How to Resolve in Python
Check our online courses!
Check our eBooks!
Subscribe to our YouTube channel!
Subscribe to our newsletter and stay up to date!
Rate this article: 
(1 votes, average: 5.00 out of 5)
Loading…
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views: 12,495