none
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

    Question

  • I am a home user and new to C# Express 2010 and SQL Server R2 2008.

    I have been writing some programs and some databases and it works very well on my computer.

    Just for fun I wanted to try to

    -          Publish the programs with C#

    -          Put it in a Web-hotel where I have a domain

    -          Try to load run it from different computers through the Internet.

    -          The program works well in development computer when I load it through Internet but not anywhere else.

     

    I get the message regarding SQL SERVER

     Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.”

     

    I read all I could find on Internet about this and tried to make adjustments so it would work but it does not (spent lots of hours on this). It seems that MS made this very hard for a newbie.

     

    This is my settings now: Data Source=XXXX-PC\SQLEXPRESS;Initial Catalog=V2_Horses;Integrated Security=True; Connect Timeout=30

    I have tried to remove Integrated; used SSPI; user and so on but no luck.

     

    Server is set to SQL server and …

     

    Services are like this started, automatic, Local system 

    I have tried changing Local system to Network for services

     

    I have enabled all protocols except VIA (TCP…)

     

    I have tried without Firewall

    I have put in port 1433 in Inbound rules and 1434 in 1434 in Outbound rules

    sqlservr.exe and sqlbrowser.exe in allowed programs and so on.

     

    I read all I could find on Internet about this and tried to make adjustments so it would work but it does not (spent lots of hours on this). It seems that MS made this very hard for a newbie and I hope there is someone out there that could give me some hints.

     

    I am using Windows7 and to C# Express 2010 and SQL Server R2 2008

     

     

     

    Thursday, June 30, 2011 2:20 PM

Answers

  • If I understand this correctly, your application runs at host provider, while the database is on your home machine.

    Trusted authentication cannot work in this scenario, because your machine is not part of the hoster's domain. Nor should it. If you want to have it this way, you must use SQL authentication.

    However, I would strongly discourage you from making an SQL Server database directly open on the Internet. That's asking for unexpected guests. And believe me, they are not sneaking in for a cup of Gevalia.

    Most host providers also supply database hosting and that's where you should bring your database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 30, 2011 9:47 PM
  • > I thought that the seqruity was so strong that just allowing a read of the database was OK. I have to start reading again and find a way to mirror the database to domain and let the user download the program from the domain in to their own computer and then accessing the database from the domain - correct ??

    The problem is that to make a database available over the Internet, you must enable SQL authentication. SQL authentication is a popular target for brute-force attacks, not the least the sa user. You can rename sa, or give it a very strong password like a GUID. This may protect you against unwanted visits. But your server will still be hammered.

    The initial upload of the database may requirements some arrangements with the hoster; their support desk should be able to help you. The updates should not be a problem. You don't to upload 1GB of data twice a day, are you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 01, 2011 9:23 PM

All replies

  • Hi,

    Make sure SQL Server is in mixed authentication mode.

    Create a SQL Server (2nd-tier login)

    Use this in the connection string.

    Disclaimer - This would NOT be suitable in a production environment. The internet should never connect direct to SQL Server. There should be Internet > Firewall > Webb App > 2nd Firewall > SQL Server

    http://msdn.microsoft.com/en-us/library/ms144284.aspx

    Hope this helps.

     


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, June 30, 2011 2:39 PM
  • Could you try it with standard security connection string as per (http://www.connectionstrings.com/sql-server-2008), i.e.

    Server=xxxx-PC\SQLExpress;Database=V2_horses;User ID=UserID;Password=Password;Trusted_Connection=False;

    You'll have to ensure SQL Servers authentication is in mixed mode, and that you've created a SQL Server login as per UserID in above connection string, with matching password and with appropriate permissions against database V2_Horses.

    Also, as it's SQL Express, your port number's not likely to be 1433 (unless you've changed it).  Make sure you have the SQL Server browser service running,

    Thanks, Andrew

    Thursday, June 30, 2011 2:45 PM
  • I have tried all the versions in your link (was on Internet).

    I have even tried without firewall and with user as the Windows auth. login to SQL.

    AS I wrote "Server is set to SQL server and …"  My problem is that any user over Internet should be able to read the database cause that  is what the program does = show information about horses. In the information on Internet 1433 for TCP and 1434 for USD was suggested. SQL BROwser is running but in local mode at the monent - tried network = no differnce. Thanks for trying to help - this is hard for a newbie.


    NIsseO
    Sorry I forgot to quote adb2303
    Thursday, June 30, 2011 6:38 PM
  • Tried that but what I want to do is let anyone over Internet reaching the database = reading. The program just show information about horses. If I understand the link right this is about known users. 


    NIsseO

    Sorry I forgot to quote Pete Carter

     

    Thursday, June 30, 2011 6:42 PM
  • If I understand this correctly, your application runs at host provider, while the database is on your home machine.

    Trusted authentication cannot work in this scenario, because your machine is not part of the hoster's domain. Nor should it. If you want to have it this way, you must use SQL authentication.

    However, I would strongly discourage you from making an SQL Server database directly open on the Internet. That's asking for unexpected guests. And believe me, they are not sneaking in for a cup of Gevalia.

    Most host providers also supply database hosting and that's where you should bring your database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 30, 2011 9:47 PM
  • If I understand this correctly, your application runs at host provider, while the database is on your home machine.

    Trusted authentication cannot work in this scenario, because your machine is not part of the hoster's domain. Nor should it. If you want to have it this way, you must use SQL authentication.

    However, I would strongly discourage you from making an SQL Server database directly open on the Internet. That's asking for unexpected guests. And believe me, they are not sneaking in for a cup of Gevalia.

    Most host providers also supply database hosting and that's where you should bring your database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Your right about all of this. As I said this was a test just to se if it works. My database is about 1 Gb big and is uppdated 2 times a day so that was why I didn't want to put in the domain.

    I thought that the seqruity was so strong that just allowing a read of the database was OK. I have to start reading again and find a way to mirror the database to domain and let the user download the program from the domain in to their own computer and then accessing the database from the domain - correct ??


    NIsseO
    Friday, July 01, 2011 5:39 AM
  • > I thought that the seqruity was so strong that just allowing a read of the database was OK. I have to start reading again and find a way to mirror the database to domain and let the user download the program from the domain in to their own computer and then accessing the database from the domain - correct ??

    The problem is that to make a database available over the Internet, you must enable SQL authentication. SQL authentication is a popular target for brute-force attacks, not the least the sa user. You can rename sa, or give it a very strong password like a GUID. This may protect you against unwanted visits. But your server will still be hammered.

    The initial upload of the database may requirements some arrangements with the hoster; their support desk should be able to help you. The updates should not be a problem. You don't to upload 1GB of data twice a day, are you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 01, 2011 9:23 PM
  • > I thought that the seqruity was so strong that just allowing a read of the database was OK. I have to start reading again and find a way to mirror the database to domain and let the user download the program from the domain in to their own computer and then accessing the database from the domain - correct ??

    The problem is that to make a database available over the Internet, you must enable SQL authentication. SQL authentication is a popular target for brute-force attacks, not the least the sa user. You can rename sa, or give it a very strong password like a GUID. This may protect you against unwanted visits. But your server will still be hammered.

    The initial upload of the database may requirements some arrangements with the hoster; their support desk should be able to help you. The updates should not be a problem. You don't to upload 1GB of data twice a day, are you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    I have started loking at possible ways around the problem: 1. Web-Matrix with Compact 2. creating some kind of Web-Service. I still want to have the program and the database on the domain but my current hoster only have MySQL. Since there is a lot of work before the information is ready to present I want to keep that part in my local computer so I have to find a way to update the database on the domain. I am a senior just  doing this for fun and to keep my brain working so any help, hints to links... is welcome. My projects stretch useally of a period of 6 - 12 month and include a lot of trial and error, reading and so on.  
    NIsseO
    Saturday, July 02, 2011 7:28 AM
  • > I have started loking at possible ways around the problem: 1. Web-Matrix with Compact 2. creating some kind of Web-Service. I still want to have the program and the database on the domain but my current hoster only have MySQL.

    Time to find another hoster. Or be hip and put it on the cloud with SQL Azure?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, July 02, 2011 8:31 AM