locked
impersonate sql RRS feed

  • Question

  • i  have a .net C# app connection to a sql databse on  a local domain and as i of know its running with a connection string that says, trusted connection = false. i need to implement sql file stream and have trusted connection = true.  i get the trusted domain error when switching to trusted connection = true. most of my user will never setup a domain and wont to use my software out of the box configured to work. when using windows authentication i need to know the steps to access sql on a untrusted domain 
    Tuesday, July 24, 2012 2:16 PM

Answers

  • We are just telling what your options are.

    I guess your problem is worksgroups. Single-user installs where all software is on the same machine should not be a problem for Window authenticaiton.

    If you want to support workgroups, you will have think quite a bit about the installer for your program. And the documentation.

    As for the 2GB that is the hard limit for varbinary(MAX) when you don't use FILESTREAM. The limit is fairly theoretical, because the performance to shove down a document that size into varbinary(MAX) through the SQL interface is likely to be awful. 1MB is usually the size which is given as the cutoff limit where FILESTREAM pays off.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Wednesday, August 1, 2012 1:53 AM
    Monday, July 30, 2012 9:31 PM

All replies

  • Hi,

    Can you post the exact error message you get?


    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Tuesday, July 24, 2012 3:18 PM
  • If your users are not expected to be running in a domain, they will log onto Windows as a local user. Essentially the domain is the computer. So if their computer is named KITCHEN and the user name is Joe, they will log onto Windows as Joe, but it will really be KITCHEN\Joe. And they will need a login in SQL Server as KITCHEN\Joe. Or you can assume they are administrators of the computer, and you can create a login for BUILTIN\Adminstrators as (previous versions of SQL Server did), but then they must start you program using the "Run as administrator" option (unless they are running Windows XP).

    So in addition to the error message, what version of SQL Server are you using, and are you targeting all versions of Windows?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, July 24, 2012 3:38 PM
  • Login failed. The login is from an untrusted domain and cannot be used with Windows authentication
    Tuesday, July 24, 2012 8:35 PM
  • i will be changing from 2008rc to 2012 to use the filetable feature, im debugging my software with a local company any i wont to role out document management but the windows auth. has been stomping me. im a beginner programmer and have just looked up online what problems have come up but this one seems to have many answers since most people setup domains or want to know about asp.net. so how do i setup a  BUILTIN\Adminstrator in sql. i can have simple instruction to run as a Administrator, most people are familiar with doing that. and what would my sql string be to access that windows login
    Tuesday, July 24, 2012 8:41 PM
  • It would be interesting to know the exact setup for the application, but if this is a product you sell, I guess the answer is that you don't know exactly how it will look like.

    In any case, the situation is a bit bleak for you. Integrated Authentication can work in three situations, as far as I know:

    1) There is a domain, so when DOMAIN\Joe logs into Windows from his workstation, the domain controler can vouch for Joe on the SQL Server machine.
    2) Application and SQL Server both run on the same machine. In this case there is no domain, as the computer itself can vocuh for Joe.
    3) There is a workgroup - that is a couple of domain-less computers in the same network - and users have the same password on their workstation as on the SQL Server machine.

    In the past, a fourth option was that the user mapped a share on the SQL Server machine, and this could help them to access SQL Server. I am not sure that this works anymore.

    Even if you would go for the latter options, it would require a lot of documentation, and you would still get a lot of support issues from Workgroup users. So it seems that you are dividing point: skip FILESTREAM/Filetable, or drop the workgroup segment of you customers. I understand that none of these are appetising.

    One possibility is to write your on server that runs on the SQL Server machine, but then that server would have to feed and recevied the documents from the users, which would remove much of the benefits with FILESTREAM and FileTable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 24, 2012 9:05 PM
  • I'm not a developer, so I don't know a lot about how you are calling this. Sorry about that.

    Regarding adding BUILTIN\ADMINISTRATOR, after your setup completes, connect to the database engine as a sysadmin (setup requires you to create at least one) and execute:
    CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS;

    That will create the login. Now, if your user starts your program with Run as administrator (to get the builtin administrator token) then a connection string with trusted connection = true and no user name or password, should work.

    One more think. The message returned on a failed login is intentionally vague to avoid handing out information to an unknown person. But, if you check the SQL Server error log, it contains more extensive information on the login failure. You may need to check that for more info.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, July 24, 2012 9:20 PM
  • i have done what you stated in your reply, i dont get the error about trusted domain but now it doesnt return anything from the database which throws a unhandled exception error. my development machine runs fine since sql is on that machine. so i pretty confident that its not a code issue. it hard to tell since i cant debug. is there some sql properties i need to set. im not using trusted connection but integrated security set to true instead. i misstated earlier. the sqlconnection string builder doesnt seem to have that property
    Wednesday, July 25, 2012 2:07 AM
  • Trusted connection = Integrated Security

    It's unlcear what you say, but it seems that now you get a different error when you connect to a remote server, while the application works if you connect to a local instance.

    That could be anything, and you will need to modify your application to have some better error handling so that you get hold of the error message from SQL Server. If the error comes from SQL Server at all, that is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 25, 2012 7:44 AM
  • ill add some code to display the sql error if any. i just wonted to make sure it wasnt the connection string first. 
    Wednesday, July 25, 2012 2:19 PM
  • As I said, it could be anything. Including the connection string.

    Getting access to error messsage is instrumental in all program development and testing. Else you are moving around in a very dark room.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 25, 2012 7:22 PM
  • where would the error log be located. its still the a untrusted domain issue
    Wednesday, July 25, 2012 10:26 PM
  • where would the error log be located. its still the a untrusted domain issue

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG

    The current error loc is ERRORLOG. ERRORLOG.1 is the immeidate past one and so on.

    The exact path can vary depending on your installation. Specifically MSSQL10.MSSQLSERVER will depend on the SQL Server version and whether this is a named instance or not.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 26, 2012 8:18 AM
  • Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

    If I am understanding correctly - you are not on a domain and you want to be able to connect to a remote SQL Server with windows authentication.

    Let's call the first system running SQL Server 'MachineA'.  The system you are running your application from 'MachineB'.

    On the SQL Server system you have added BUILTIN\Administrators.

    On MachineA, you have a user created as UserA and added that user as a local administrator.  You then login to MachineA as UserA, run as administrator and it should work.  The authentication will be MachineA\UserA...

    On MachineB, you have a user created as UserB and added that user as a local administrator.  You then login to MachineB as UserB, run as administrator it will fail.  The authentication will be MachineB\UserB - which does not have local administrator rights to MachineA or SQL Server.

    At this point, you are going to try to add UserB to MachineA and find out that is not possible because you don't have a domain.  What you can do at this point is add UserA to MachineB *with the same password* on both systems.  Then login to MachineB with UserA and this should now work...

    In other words, you don't need BUILTIN\Administrators...what you need is every user added to MachineA as a user and a matching user on MachineB with the same password on both systems. Then, you add the local user from MachineA to SQL Server with appropriate permissions.

    If the user changes their password on MachineB - the password must be changed on MachineA also or it will break.


    Jeff Williams

    Sunday, July 29, 2012 3:01 PM
  • thanks but for my local use that that would be ok. but most users wouldnt have a clue how to set up users. since most my users are not tech savy and have very little it support couldn't i just save the documents in varbinary(max). most document never exceed the 2gb size. i couldnt find a single word excel or pdf that was close to that size on my computer. im using the sql express for single users, i just would have to have them bye a single user license from sql. this way i could just use the sql login that im currently using.
    Monday, July 30, 2012 12:11 PM
  • We are just telling what your options are.

    I guess your problem is worksgroups. Single-user installs where all software is on the same machine should not be a problem for Window authenticaiton.

    If you want to support workgroups, you will have think quite a bit about the installer for your program. And the documentation.

    As for the 2GB that is the hard limit for varbinary(MAX) when you don't use FILESTREAM. The limit is fairly theoretical, because the performance to shove down a document that size into varbinary(MAX) through the SQL interface is likely to be awful. 1MB is usually the size which is given as the cutoff limit where FILESTREAM pays off.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Wednesday, August 1, 2012 1:53 AM
    Monday, July 30, 2012 9:31 PM