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

    Question

  • Greetings community

    First, I apology if I missed the forum. Maybe I should put it in some Windows 7 forum, but it’s about SQL Server Express, so forgive me if I’m wrong.

    So, I have two machines running Windows 7 Professional 64 bit, one desktop, and one laptop. Both machines have only one user with admin privileges. Those machines are in homegroup. I could login on any machine from another using username and password of the machine I want to access.

    Now, on desktop machine I installed SQL Server 2012 express 64 bit version which includes SQL Server Management Studio.

    On desktop, in Windows firewall’s advanced settings I added two incoming rules: one for TCP port 1433, and one for UDP port 1434, both with profile private and domain. For firewall it appears to be enough: I made one simple application using System.Data.Sql.SqlDataSourceEnumerator, installed it on client (laptop), and it sees my named instance of SQL server express.

    So, I made one simple database with only one table in it. Then, I made one simple .NET application, dragging that table from datasource to the form. Connection String is like

    Data Source=desktop\SQLEXPRESS12;Initial Catalog=TestDB;Integrated Security=True;User ID=sa;Password=*****;Connect Timeout=30;User Instance=False

    And I’ve been changing it with user (putting user name of windows account on desktop and password that I type in when Windows starts) which works when I start application on desktop, but when I installed application on laptop, whatever I use as UserID, it fails with error said in the title of this question.

    Having the fact that SQL Server 2012 Express was released after Windows 7, and is meant to be used at home, there must be some way to configure it to run in homegroup. What I obviously need is the way to transfer user (credential… whatever) from client/laptop to server/desktop, or maybe the way to put both machines in one domain not ruining the homegroup, but I don’t have a distant clue how to do that.

    So, If anyone have any idea, please help.

    Tuesday, August 21, 2012 1:12 AM

Answers

  • Those machines are in homegroup. ....

    Data Source=desktop\SQLEXPRESS12;Initial Catalog=TestDB;Integrated Security=True;User ID=sa;Password=*****;Connect Timeout=30;User Instance=False

    Hello,

    In your connection string you use both login modes: Windows (=integrated sec) and SQL Account login (=User Id +PWD), that's wrong and the Windows login will be used. But because you have a workgroup, you can't use Windows login and therefore you get the error message.

    You have to use SQL login and for this simply change your connection string to:

    Data Source=desktop\SQLEXPRESS12;Initial Catalog=TestDB;Integrated Security=False;User ID=sa;Password=*****;Connect Timeout=30;User Instance=False


    Olaf Helper
    Blog Xing

    Tuesday, August 21, 2012 4:35 AM

All replies

  • Those machines are in homegroup. ....

    Data Source=desktop\SQLEXPRESS12;Initial Catalog=TestDB;Integrated Security=True;User ID=sa;Password=*****;Connect Timeout=30;User Instance=False

    Hello,

    In your connection string you use both login modes: Windows (=integrated sec) and SQL Account login (=User Id +PWD), that's wrong and the Windows login will be used. But because you have a workgroup, you can't use Windows login and therefore you get the error message.

    You have to use SQL login and for this simply change your connection string to:

    Data Source=desktop\SQLEXPRESS12;Initial Catalog=TestDB;Integrated Security=False;User ID=sa;Password=*****;Connect Timeout=30;User Instance=False


    Olaf Helper
    Blog Xing

    Tuesday, August 21, 2012 4:35 AM
  • Thanks Olaf, this definitely works. Thanks for the answer.

    If I’ve understood correctly, integrated security is about logon to SQL with windows accounts. I’m simply curious: could I create a user different than sa, with a little lower permissions and use it to connect from the other machine? Does user, that is meant to be connected from other machine, have to be the part of server security construct, or I could create it in the database’s security?

    Tuesday, August 21, 2012 7:34 PM
  • could I create a user different than sa, with a little lower permissions

    Hello,

    Yes, you could and you should; never use the SysAdmin account "sa" for any application.

    Add a new SQL Login. add this login as database user and grant the minimum permission that the account needs for its work.

    Here an T-SQL example; it adds also the database user to the "db_datareader" db role, so that it can read from all tables in the database, but don't have permission to change any data:

    USE [master]
    GO
    CREATE LOGIN [MyAppAccount] WITH PASSWORD=N'AVeryStr0ngPa$$W0rd';
    GO
    
    USE [AdventureWorks]
    GO
    
    CREATE USER [MyAppAccount] FOR LOGIN [MyAppAccount]
    EXEC sp_addrolemember N'db_datareader', N'MyAppAccount'
    GO
    


    Olaf Helper
    Blog Xing

    Wednesday, August 22, 2012 5:15 PM
  • Thanks again Olaf.
    Thursday, August 23, 2012 5:42 AM