Azure VM SQL Server connection string

0
votes

*** Apologies folks - I appended wrong code ---now replaced below here

I have a simple Visual Studio .NET web forms app. I run it on my Azure VM called dexram (Windows 10) and I also created a SQL Server on the Azure VM. There is a user on the VM called 5001211 that has admin authority in Windows. It can use SSMS to access the database no problems.

All my connection string attempts fail in the C# code. This is strange as the VS web app and the SQL Server are both running on the Azure VM. Here are the strings I tried and the messages I got underneath:

string Server = "Data Source = dexram; Initial Catalog = FruitNVeg; User ID=5001211;Password=Fitsh3ly;";

This connection string throws an error:

Login failed for user '5001211'

string Server = "Data Source = tcp:dexram,1433; Database = FruitNVeg; User ID = 5001211@dexram; Password = Fitsh3ly; Trusted_Connection = False; Encrypt = True;";

The certificate chain was issued by an authority that is not trusted

string Server = "Data Source = tcp:dexram,1433; Authentication = Active Directory Integrated; Database =  FruitNVeg;";

The certificate chain was issued by an authority that is not trusted

string Server = "Data Source = tcp:dexram,1433; Authentication = Active Directory Password; Database =  FruitNVeg; UID=5001211@dexram;PWD=Fitsh3ly;";

The certificate chain was issued by an authority that is not trusted

enter image description here

Thanks Dan - no luck - I created as per your suggestion and made 5001211 sysadmin and got following results:
string Server = "Data Source = dexram; Initial Catalog = FruitNVeg; User ID=5001211;Password=Fitsh3ly;";
Gives -- > Login failed for user '5001211'

string Server = "Data Source = tcp:dexram,1433; Database = FruitNVeg; User ID = 5001211@dexram; Password = Fitsh3ly; Trusted_Connection = True; Encrypt = True;";

Gives -- > The certificate chain was issued by an authority that is not trusted

I am thinking I need to get a cert. created as I think (?) my SQL calls from my VS app are going out over the internet (even though the 2 tools (VS and SQL Svr) are on the same VM machine) ?

3
sql-serverazurevirtual-machine

3 Answers

0
votes

you must first create a user in sql server after use from string format below

Data Source=instanse name or use .;Initial Catalog=database bame;User ID=created user in sql server;Password=your password

enter image description here

and do setting below for user enter image description here enter image description here

0
votes

User dexram\5001211 is a Windows account. Your app connection string specifies a SQL login named 5001211. You need to create a SQL login named 5001211 and an associated database user:

USE FruitNVeg;
CREATE LOGIN [5001211]  WITH PASSWORD = 'Fitsh3ly';
CREATE USER [5001211];

The user will also need permissions on the objects the application uses in the FruitNVeg database. Although you could add the login to a privileged role like sysadmin to avoid granting these permissions, the best practice is to use a minimally privileged account for routine application database access that has only the required permissions:

USE FruitNVeg;
GRANT SELECT ON dbo.Apples TO [5001211];
0
votes

As per this URL --> https://blog.greglow.com/2020/01/16/sql-sql-server-the-certificate-chain-was-issued-by-an-authority-that-is-not-trusted/

I used the sql config manager and set "Trust Server Cert" to yes and that fixed the problem it seems