🔒 Locked

SQL Server Express login problem

Started by ktuluorion Mar 31, 2009 at 6:23 PM 5 replies 1.4k views

Original Post

222

Mar 31, 2009 at 6:23 PM

Hey All! I am developing a .net application in which i connect to a sql server database. I'm a newb with working with sql server, but i got it up and running with data in it. The problem is that I have windows authentication set as the method of authentication. When I run the program on the machine that houses sql server, it works fine (as it is logged in as the admin for sql server). The problem is that when i put the program on another machine on the network, it refuses to login (i'm guessing because it is logged in as something liek "machine2\someuser" instead of "machine1\someuserprovisionedforsql". So how is this supposed to work? I'm guessing that it is viewing the external machine as a "Guest," but what i can't figure out is how to configure sql server express to allow for that. THanks for any help!

Nik02

276

Nik02

Apr 1, 2009 at 6:32 AM

Have you used the Surface Area Configuration tool to enable the SQL Server network protocols, and have you adjusted your firewall to enable incoming traffic to ports of said protocols (both are operations on the machine hosting the SQL Server)?

In addition, remember to modify your connection string (on the client) to point to the server machine instead of localhost.

Codeka

155

Codeka

Apr 1, 2009 at 6:40 AM

Cross machine authentication doesn't really work if you're not part of a domain. You might be able to fool it if you create a user on both machines with the same username and password and login using that. Other than that, I would suggest SQL Server authentication.

evolutional

Mod

393

evolutional

Apr 1, 2009 at 7:19 AM

Have you added the new user to the SqlServer instance as a login? You may want to create a Role and assign logins to that.

owl

267

owl

Apr 1, 2009 at 7:29 AM

Quote:
Original post by evolutional
Have you added the new user to the SqlServer instance as a login? You may want to create a Role and assign logins to that.

yes. He can create a login named "machine2\someuser" even if the machine2 isn't registered in the machine that runs the server, isn't?.

Anyway, wouldn't it be easier to use sql server authentication instead of windows authentication?

evolutional

Mod

393

evolutional

Apr 1, 2009 at 4:20 PM

Quote:
Original post by owl
Anyway, wouldn't it be easier to use sql server authentication instead of windows authentication?

It depends on the context. A Sql Server login will grant anyone with the username/password the ability to access the system, whereas the windows auth system allows only people who can log in to authenticate and allows you to control authentication for specific users.

For something that doesn't directly access the Sql Server, I'd say Sql Server logins are accecptable (although I'd still prefer Windows Auth and Sql Roles) - but for something accessing Sql from an unknown location, a Sql Server login is kinda scary :)

Codeka

155

Codeka

Apr 1, 2009 at 5:25 PM

Windows authentication does provide some benefits. For example, you don't need to specify username/passwords for connection to SQL Server in your application's configuration.

However, the main limitation is as I said: it doesn't really work for cross-machine authentication unless you're joined to a domain. You can't just say "machine2\username" in SQL Server, because it has no way to authenticate users from machine2: all the information required to do that is stored on machine2. The only work around that I'm aware of (short of joining to a domain) is to create a user on both machine1 and machine2 with the same username and password, which will work, but is arguably not that secure (for example, it becomes difficult to change the password)

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.