question

raki-3122 avatar image
0 Votes"
raki-3122 asked AvetikBanduryan-2447 commented

"ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Hello All,
i am trying to connect to sql server database using ODBC connection string. i am fine when i access application with local sql server. but problem is when connecting to another standalone sql server. the error i am getting is "err.Message = "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" i have tried installing with both 32/64 bit version of ODBC driver but getting same error. FYI, i was able to do test connection successfully using odbc to that sql server. problem is when trying to access the application from my local machine to that sql server. As you can below is the error i am getting.

 <add name="Test" connectionString ="Data Source=JohnTest;Initial Catalog=Department;User ID=123;Password=123;"/>

55843-image.png



sql-server-generalsql-server-transact-sql
image.png (36.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered AvetikBanduryan-2447 commented

Hi @raki-3122,

Try to configure both user DSN and System DSN for this connection in the ODBC data source, and create DSN and test in both 32-bit/64-bit ODBC data source.

Or also try to directly specify the driver name in connection string.
https://www.connectionstrings.com/microsoft-odbc-driver-13-for-sql-server/

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @raki-3122,

But question is, if i can access the database using same odbc connection from visual studio on the server itself why not from IIS when residing there.

This problem seems to be related to IIS. I suggest you go to the IIS forum (dotnet-aspnet-iisnet) for proper help.
https://docs.microsoft.com/en-us/answers/topics/dotnet-aspnet-iisnet.html

1 Vote 1 ·

"Try to configure both user's DSN and System DSN for this connection in the ODBC data source, and create DSN and test in both 32-bit/64-bit ODBC data source" solution works for my local SQL Server 2017 which was installed on my laptop.

Thanks for the solution.

1 Vote 1 ·
AS-6265 avatar image
0 Votes"
AS-6265 answered

I think you need to add the Server paramater to your connection string so for example "server=ServerA;Data Source=JohnTest...."

"..." is the same as you have and you can use an IP address instead of ServerA if you prefer.

Are you doing the test connection from the ODBC control panel screen and selecting or specifying the server name?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

raki-3122 avatar image
0 Votes"
raki-3122 answered AS-6265 commented

Hello As-6265,

Thanks for your reply. i have tried with server name parameter as well ip address, nothing is working. yes i have done testing with odbc control panel using server name and i was able to connect. what could be the issue?

Note: i am using sql server authentication to access the sql server.

<add name="Test" connectionString ="Server=Tower; Database=Colon;User ID=test1;Password=test1;"/>

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry I'm not completetely sure.

This site https://www.connectionstrings.com/sql-server/ might be useful

I haven't got a system I can test on at the moment but the ODBC examples on the link (example https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/) use different parameter names (database instead of data source for example). That might be worth looking at. I think your connectionstring might be for a different driver type (Microsoft SQL Client perhaps) rather than ODBC.

Apologises I looked at that site before posting my original answer but didn't realise I wasn't looking at the ODBC information.


1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

If you connect by ODBC, you either give a DSN which then has most of the configuation. Or you specify all in the connection string, and you start with the driver. For instance:

Driver={ODBC Driver 17 for SQL Server}

But then that depends on exactly which version of the ODBC driver you are using.

Then again, any particular reason you are using ODBC? The only reason to use ODBC is if you want to target multiple database engines, and not only SQL Server. Else you would use SqlClient and not OdbcClient.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

raki-3122 avatar image
0 Votes"
raki-3122 answered raki-3122 edited

Hello All,

now i can access the sql server from my development workstation (windows 10) using below ODBC but having same issue when trying to access from Azure web server which is windows server 2019 datacenter. any idea what could be the issue?

Update: i just noticed if i run/debug the application with visual studio on the azure web server i can get to the sql server but sitting into iis giving same error.

56343-image.png



image.png (45.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

The issue could be that you have not told us what connection string you use on the web server, to which instance you are trying to connect.

Or maybe the issue is that or crystal balls are dusty and full of scratches, so we cannot clearly see what you are doing.

If I am going to make a guess out of the blue, you set up a user DSN so then it works when you run from your own user, but not when you run from the web server that runs as a service. A system DSN may work. Or a DSN-less connection string with server etc specified. And driver.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

raki-3122 avatar image
0 Votes"
raki-3122 answered ErlandSommarskog commented

Below is the connection string i am using and its system DSN.

<add name="Newton" connectionString="DRIVER={ODBC Driver 13 for SQL Server};Server=test1;Database=manhattan;Uid=123;Pwd=123"/>

But question is, if i can access the database using same odbc connection from visual studio on the server itself why not from IIS when residing there.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And what error message do you get?

I hope that the user name and password are not authentic.

1 Vote 1 ·