Difficulty Connect to SQL Server Express on LAN

Question

I'm having trouble connecting to a SQL Server on a machine in our LAN.

i have done the following :

1.) Made the IP of the machine running the Server 10.0.0.7 and made the IP of the other machines on the LAN IP 10.0.0.X . There is no internet on the LAN(and it will stay that way), changeing IP is permitted.

2.)Enabled remote connection by doing This

3.)Used the appropriate connection string.

Now my only Question that remains is regards to the connection string of those who wish to connect on the server, here is the current Con-string:

string sConnection = @"Server=10.0.0.7\MARNUS-PC\MARNUS_HOME; User ID=MARNUS-PC\MARNUS_HOME; Password=somepassword; Initial Catalog=TestDB;";

My questions are :

1.)What do i put the Server portion? here is my Server name and instance name acording to the management studio - Server name : MARNUS-PC\MARNUS_HOME , Instance name : MARNUS_HOME.

2.)What do i use as user ID? I wish to use Windows authentication instead of sql server authentication(i have enabled both on server), so how should the connection string change and how to do i allow a user from the server's side on the management studio(if needed)?

3.)I asume if I use Windows authentication i do not need the password part of the Con-String?

I apologize for all the questions,but i was taught MS Access and is now making the switch to SQL Server by myself.

M.Ali · Accepted Answer · 2014-03-09T13:34:58.370

2

Your connection string must be something like....

string sConnection = @"Server=MARNUS-PC\InstanceName; User ID=MARNUS-PC\MARNUS_HOME; Password=somepassword; Initial Catalog=TestDB;";

Instance name is the Sql Server Instance name.

If you are not Sure about your instance name execute the following statement in your Sql Server Management Studio

SELECT @@SERVERNAME

It will return the [ServerName\InstanceName] you can pass the whole returned string to your connection string are your Server name Server=ServerName\InstanceName;

Windows Authentication

To use windows authentication you will have to a connection string something like this..

 string sConnection = @"Server=MARNUS-PC\InstanceName;  Initial Catalog=TestDB; integrated security=SSPI";

edited Mar 09 '14 at 13:34

answered Mar 09 '14 at 13:21

M.Ali

67,945
13
101
127

Got the Instance name, and inserted it where i should, how ever how do i use windows authentication? – Mordacai1000 Mar 09 '14 at 13:29
I see, understood. is it required that i add the the other PC's on some kind of permission list? For instance if the the user STEVE-PC wishes to connect and the simply types the mentioned Connection string, will he have access? – Mordacai1000 Mar 09 '14 at 13:35
This is only the authentication for a user. (obviously windows credentials will be used). But for permission(What a user can do inside database once connected) it depends what role this user has been assigned to and what permission that role has in sql server. – M.Ali Mar 09 '14 at 13:38
1

@Mordacai1000 Authentication and Permission are two different things. Authentication deals with question `WHO ARE YOU?` with windows Authentication windows credentials will confirm who are you. Permission deal with the Question `WHAT CAN YOU DO ONCE CONNECTED?` this will be answered by the role assigned to you in a database. – M.Ali Mar 09 '14 at 13:44
At this moment i am the only allowed user on the sql server. I would just like to confirm how to add another user for permission. 1.)Rightclick on database => Properties => Permissions => Search? – Mordacai1000 Mar 09 '14 at 13:50
No I think it is Database --> go to `Security Folder` for that database and then `Users` and then `add new user`. – M.Ali Mar 09 '14 at 13:56
Have a look at this question [`Granting Permission In Sql Server`](http://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username) – M.Ali Mar 09 '14 at 13:57
Got it. So the connection string is is sufficient as it is now (string sConnection = @"Server=MARNUS-PC\InstanceName; Initial Catalog=TestDB; integrated security=SSPI";) ? – Mordacai1000 Mar 09 '14 at 13:59
Connection string is only the address to a server/source you want to connect to. using a certain authentication type to tell the source who are you. It has nothing to do with the permission of what you can do inside the database. Thats altogether a separate layer of security in sql server. – M.Ali Mar 09 '14 at 14:02

score 2 · Answer 2 · answered Mar 09 '14 at 13:23

The IP address identifies the computer, so you wouldn't use both the IP address and the computer name. You can use the IP address in the server setting:

Server=10.0.0.6\MARNUS_HOME;

or you can use the computer name:

Server=MARNUS-PC\MARNUS_HOME;

You are correct that you shouldn't specify any password when you are using windows authentication, and you shouldn't specify any user name either, but you need to specify that you are using it:

Trusted_Connection=Yes;

Difficulty Connect to SQL Server Express on LAN

2 Answers2