How to connect to a new server with similar old connection string info

3

Here in the company I'm working, we have migrated the current DB table structure and the Stored Procedures to a new DB; working so far.

The problem we are facing is that there isn't any way to get our app connected to the new DB. The reason? Probably some problem with the connection strings saved in the App.config (xml) file.

It's strange, because even if we have provided a similar Data Source, User ID, Password and Server structure as we had with the old DB, it simply refuses to connect and do any with it's functions.

The Database is running on SQL Server 2017 (same as before), and our app is developed in Winforms C#, using Entity Framework 6, and the EXECSQLSERVERPROCEDURE NuGet plugin.

This plugin in particular uses a special connection string (detailed below with the others) that doesn't use a "connectionString", but a "value" instead, and it's one of the parts that's failing, as well as the TableAdapter-based, filled DataGridViews. Only the EF6 connections and functions are working correctly.

I tried, from changing the Server Instance name, according to: https://sqldbpool.com/2008/09/03/how-to-change-sql-server-instance-name/

So I didn't have to deal with special chars in the App.config; using HTML codes and entities for the Named Server Instance in Data Source (even in password with a asterisk, which we changed later without any effect), using this as reference: https://www.toptal.com/designers/htmlarrows/

And, changed the reference of the special Connection String for a regular one, calling by "name" instead of "key", because this one doesn't exist in regular Connection String definition (yeah, tried to add it, but didn't work).

Just to get a big amount of nothing and frustration (being on this one since Friday, just to put you all in context).

Connection Strings (in App.config):

<connectionStrings>
    <add name="MYAPP.Properties.Settings.MYAPP" connectionString="Data Source=MYAPPSERVER;Initial Catalog=MYAPP;User ID=sa;Password=MYAPP2019;Server=192.168.*.*"
      providerName="System.Data.SqlClient" />
    <add name="MYAPPBD" connectionString="metadata=res://*/MYAPP.csdl|res://*/MYAPP.ssdl|res://*/MYAPP.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=192.168.*.*\MYAPP;initial catalog=MYAPP;user id=sa;password=MYAPP2019;multipleactiveresultsets=True;application name=EntityFramework&quot;"
      providerName="System.Data.EntityClient" />
</connectionStrings>
<appSettings>
    <add key="DBSQL" value="workstation id=server;packet size=4096;data source=MYAPPSERVER;initial catalog=MYAPP;user id=sa;password=MYAPP2019;server=192.168.*.*" />
    <add key="ClientSettingsProvider.ServiceUri" value="" />
</appSettings>

The Definition of the Function that uses this "DBSQL" special Connection String (for getting a User's Privileges):

Dictionary<string, object> param = new Dictionary<string, object>();
DataTable dt = new DataTable();
param.Add(new SqlParameter("@profile", profile));

dt = SQLSERVERPROCEDURE.SQLSERVER.Exec("DBSQL", "spRec_Privileges", param, dt);

You can see this plugin source code here, for reference: https://www.nuget.org/packages/EXECSQLSERVERPROCEDURE/

Expecting to connect to DB with all functions and not only EF6 ones, because we can't parse DataTable / DataSet returning functions to do the same as they do with EF List's. As for the auto-filled with TableAdapters' DataGridViews, we can make SP's for loading and saving changes, but that would consume time we can't spare for it.

This is the error, even if User ID and Password are well-typed:

System.Data.SqlClient.SqlException
   HResult = 0x80131904
   Message = Network-related or instance-specific error while establishing a connection to the SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to support remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection with SQL Server)
   Source = .Net SqlClient Data Provider
   Battery Tracking:
    in SQLSERVERPROCEDURE.SQLSERVER.Exec (String ConnectionName, String Procedure, Dictionary`2 Variable and Values, DataTable dt)
    in MYAPP.Class.User.GET_Usu_Privileges (Int32 profile) in C: \ Users \ User \ source \ repos \ MYAPP - EF Integration  \ MYAPP \ Class \ Users.cs: line 107

Internal Exception 1:
Win32Exception: Username or password are not correct

Edit: Queries among the DB works well. Even if using full Server-Name\Instance format, which is the original one, doesn't work. We aren't using Windows auth (this is only being used in Server); just SQL Server auth.

c#
sql-server
xml
winforms
entity-framework
asked on Stack Overflow Oct 15, 2019 by Wolney Sandoval • edited Oct 15, 2019 by Wolney Sandoval

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0