How to connect two SQL Server using private network.

Hi All:
I have two SQL Servers physically sitting next to each other. These servers
have Public IPs as 10.10.49.123 and 10.10.49.124. These two servers are also
connected through Cross Over Cables for Private Network and private non
routed IPs are 192.168.1.2 and 192.168.1.3. I have SQL Serever Transactional
Replication set up on it and I want to use private network for transferring
data between these two servers. Private network is on 2 Gigabit switch. Non
replication traffic can come vis normal way as it coming currently. Is it
possbile to implement this kind of setup or not? If so, how? Any help or
whitepaper on it will be highly appreciated.
Server 1 is Publisher and Distributor
Server 2 is Subscriber."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:DADD0D4A-5010-4A57-ADDA-DE898FCCE68D@.microsoft.com...
> Hi All:
> I have two SQL Servers physically sitting next to each other. These
> servers
> have Public IPs as 10.10.49.123 and 10.10.49.124. These two servers are
> also
> connected through Cross Over Cables for Private Network and private non
> routed IPs are 192.168.1.2 and 192.168.1.3. I have SQL Serever
> Transactional
> Replication set up on it and I want to use private network for
> transferring
> data between these two servers. Private network is on 2 Gigabit switch.
> Non
> replication traffic can come vis normal way as it coming currently. Is it
> possbile to implement this kind of setup or not? If so, how? Any help or
> whitepaper on it will be highly appreciated.
> Server 1 is Publisher and Distributor
> Server 2 is Subscriber.
>
Just put entries in the hosts file of each server so when Server 1 goes to
connect to Server 2, its name resolves to 192.168.1.2 and not 10.10.49.123.
David|||I tried this and this didnt work. Why is it that from command prompt on
Server 1, I can ping Server 2's private IP address and yet when I add them
into Host File and then try, it fails.
Anything else that I need to do other than adding entries into Host file?
This is what I did so far.
"David Browne" wrote:

> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:DADD0D4A-5010-4A57-ADDA-DE898FCCE68D@.microsoft.com...
> Just put entries in the hosts file of each server so when Server 1 goes to
> connect to Server 2, its name resolves to 192.168.1.2 and not 10.10.49.123
.
> David
>
>|||After you add the entry to the host file, if you ping the server by name
which IP is used?
Have you rebooted after changing the host file?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:F4C36A18-2D24-4A63-96A6-906126167A12@.microsoft.com...
> I tried this and this didnt work. Why is it that from command prompt on
> Server 1, I can ping Server 2's private IP address and yet when I add them
> into Host File and then try, it fails.
> Anything else that I need to do other than adding entries into Host file?
> This is what I did so far.
>
> "David Browne" wrote:
>
are
non
switch.
it
or
to
10.10.49.123.|||Thanks to both for replying to my problem.
It still shows 10.10.49.124. When I try to conenct to SQL Server using
private IP address in Query Analyzer, I get error saying: Unable to connect
to SQL Server. SQL Server doesn't exist or Access denied.
Is it cached or am I missing something.
So, to clearly understand this, all I have to do is add entry in the host
file to the private IP address and it should worlk. Correct' Anything else?
"Jim Underwood" wrote:

> After you add the entry to the host file, if you ping the server by name
> which IP is used?
> Have you rebooted after changing the host file?
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:F4C36A18-2D24-4A63-96A6-906126167A12@.microsoft.com...
> are
> non
> switch.
> it
> or
> to
> 10.10.49.123.
>
>|||I believe adding the IP to the host file will allow you to refernce the
server by name instead of IP. I would expect that the IP address would work
regardless, assuming your network is setup correctly.
Are you even able to ping the server by IP address? If not then your
network is not set up correctly.
Are you running query analyzer on the server, or on a client that is not
part of the private network?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:E0B6F1EC-FFE5-4E5B-946F-4E34BE507D71@.microsoft.com...
> Thanks to both for replying to my problem.
> It still shows 10.10.49.124. When I try to conenct to SQL Server using
> private IP address in Query Analyzer, I get error saying: Unable to
connect
> to SQL Server. SQL Server doesn't exist or Access denied.
> Is it cached or am I missing something.
> So, to clearly understand this, all I have to do is add entry in the host
> file to the private IP address and it should worlk. Correct' Anything
else?
> "Jim Underwood" wrote:
>
on
them
file?
These
servers
private
currently. Is
help
goes|||Correct, but it sounds like you have a different issue. If you can't
connect to Server 2 from Server 1 using the internal ip address, then
the host name won't work either. Sounds like either a bad cross-over
cable, or the internal network cards are disabled.

>From server1, with an IP Address of 192.168.1.2, get a command prompt
and ping the other internal IP address (192.168.1.3). If you don't get
a connection, then you got network issues.
Stu|||Thanks guys. Let me work on it and I will put my feedback here. I have to us
e
different cables and see what happens next. It could be a hardware issue mor
e
than a software.
Once again thanks a lot for all your help
"Stu" wrote:

> Correct, but it sounds like you have a different issue. If you can't
> connect to Server 2 from Server 1 using the internal ip address, then
> the host name won't work either. Sounds like either a bad cross-over
> cable, or the internal network cards are disabled.
>
> and ping the other internal IP address (192.168.1.3). If you don't get
> a connection, then you got network issues.
> Stu
>

HOW to connect SQL Server in another LAN Network

Hi Friends,
I have two LAN networks, having different IP Address classes(say one
network is 101.12.12.x and other is 102.12.11.x). These LAn Networs are
connected by the Internet connection. At both the networks routers are
configured and at the Gateway Firewall Port 1433 is opened.
How to connect the SQL server of other network from network?
With regards,
Lalit
Hi Lalit,
did you try to call the stored procedures sp_addlinkedserver SERVERNAME
and then sp_addlinkedsrvlogin LOCAL_LOGIN, REMOTE_LOGIN, REMOTE_PWD
regards
Andreas
"Lalit" <Lalit@.discussions.microsoft.com> wrote in message
news:B2B9DF70-6D4D-44D2-A56D-79224A89748A@.microsoft.com...
> Hi Friends,
> I have two LAN networks, having different IP Address classes(say one
> network is 101.12.12.x and other is 102.12.11.x). These LAn Networs are
> connected by the Internet connection. At both the networks routers are
> configured and at the Gateway Firewall Port 1433 is opened.
> How to connect the SQL server of other network from network?
>
> With regards,
> Lalit
|||Hi Andreas,
Thanks for response.
I have tried normal TCP IP connection from the Qery Analyser. Where it is
not working.
With Regards,
Lalit
"Andreas Müller" wrote:

> Hi Lalit,
> did you try to call the stored procedures sp_addlinkedserver SERVERNAME
> and then sp_addlinkedsrvlogin LOCAL_LOGIN, REMOTE_LOGIN, REMOTE_PWD
> regards
> Andreas
> "Lalit" <Lalit@.discussions.microsoft.com> wrote in message
> news:B2B9DF70-6D4D-44D2-A56D-79224A89748A@.microsoft.com...
>
>

HOW to connect SQL Server in another LAN Network

Hi Friends,
I have two LAN networks, having different IP Address classes(say one
network is 101.12.12.x and other is 102.12.11.x). These LAn Networs are
connected by the Internet connection. At both the networks routers are
configured and at the Gateway Firewall Port 1433 is opened.
How to connect the SQL server of other network from network?
With regards,
LalitHi Lalit,
did you try to call the stored procedures sp_addlinkedserver SERVERNAME
and then sp_addlinkedsrvlogin LOCAL_LOGIN, REMOTE_LOGIN, REMOTE_PWD
regards
Andreas
"Lalit" <Lalit@.discussions.microsoft.com> wrote in message
news:B2B9DF70-6D4D-44D2-A56D-79224A89748A@.microsoft.com...
> Hi Friends,
> I have two LAN networks, having different IP Address classes(say one
> network is 101.12.12.x and other is 102.12.11.x). These LAn Networs are
> connected by the Internet connection. At both the networks routers are
> configured and at the Gateway Firewall Port 1433 is opened.
> How to connect the SQL server of other network from network?
>
> With regards,
> Lalit|||Hi Andreas,
Thanks for response.
I have tried normal TCP IP connection from the Qery Analyser. Where it is
not working.
With Regards,
Lalit
"Andreas Müller" wrote:

> Hi Lalit,
> did you try to call the stored procedures sp_addlinkedserver SERVERNAME
> and then sp_addlinkedsrvlogin LOCAL_LOGIN, REMOTE_LOGIN, REMOTE_PWD
> regards
> Andreas
> "Lalit" <Lalit@.discussions.microsoft.com> wrote in message
> news:B2B9DF70-6D4D-44D2-A56D-79224A89748A@.microsoft.com...
>
>

How To Connect SQL Server

hi,
plz help how to connect sql server from one network to other using vpn with different ip schemes.
Thankshi,

plz help how to connect sql server from one network to other using vpn with different ip schemes.

Thanks

Try connecting to the ip address instead of the servername. Not sure of your question.|||Ok i explain, i have two locations conneted through VPN,Both side have different IP Schemes NetWork (190.168.0.1),(193.168.0.1).For Routing Purpose We are using a workstation with 2 Network card one of his own net work connectivity and other for to connected to other network.When we connected these two workstations, they work fine,Sql server also work fine.but when any user of both networks want to connect Other Network sqlserver "Error Server Does Not Exist"

Any Idea How to Connect.

Thanks|||There are more than one way to connect sql server using VPN
Steps:-
1. save the ip in driver file
2. open Remote Desktop Connections(mstsc.exe)
3. enter the ip (make sure you are using VPN)

one more way:-
you can register that locally to your client(SQL server client)
if want to know details, write me @.sumitsahay@.gmail.com|||Are you able to ping that ip from that computer where u want connectivitysql

How to configure SQLExpress on a workgroup

I recently deployed an app with SQLExpress2005 as the backend. Despite
testing it here on my dev network, this was my first opportunity to use it
in a workgroup environment. I had to really jump through some hoops to get
connectivity between the app and the SQLExpress database. I'm asking for any
advice or constructive criticism on the steps I took, particularly in
regards to whether it was unnecessary or exposed any security issues.
Scenario:
-- Four PCs (all XP Pro) in a workgroup configuration -- no server,
connectivity is through a router.
-- SQLExpress installed on one of the PCs with a server instance
"PC1\SQLExpress".
-- Database "AppDB" attached to "PC1\SQLExpress".
-- A .Net application is installed on all workgroup PCs. The app works "as
advertised" on the PC where there SQLExpress is installed, with connection
string security set to SSPI.
Could not get connection between the apps on the other PCs to "AppDB",
sooooo ...
On the SQLExpress PC:
1. Ensured firewall had ports 1433 and 1434 exposed.
2. Added ALL the local workgroup users and their passwords to the XP machine
(PC1).
3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
over TCP/IP only.
4. Added "PC1\GUEST to SQLExpress Logins.
On the downrange PCs, the app now reported, "Cannot open database "AppDB"
requested by login."
5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ ->
Properties -> UserMapping and checked Map "AppDB"
On the downrange PCs, the app now reported, "EXECUTE permission denied on
"sel_Employees". So it appears that the user mapping WAS required.
6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
Database Role Membership and set to db_owner.
The application on all downrange PCs now connect and function as advertised!
This sure strikes me as incredibly Rube Goldberg in order to get connection
between local network apps and an SQLExpress database. It seems like a DBA
would be required even for SQLExpress installs, which surely is not what
Microsoft intended. What could I have done better or easier?How about having your app using a SQL Server login, perhaps with a password not known to the users
of the other machines? that would cut off a lot of the steps you took.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <brikshoe@.newsgroups.nospam> wrote in message news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite testing it here on my dev
>network, this was my first opportunity to use it in a workgroup environment. I had to really jump
>through some hoops to get connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in regards to whether it was
>unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server, connectivity is through a
> router.
> -- SQLExpress installed on one of the PCs with a server instance "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as advertised" on the PC
> where there SQLExpress is installed, with connection string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB", sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB" requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ -> Properties ->
> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on "sel_Employees". So it
> appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties -> Database Role Membership
> and set to db_owner.
> The application on all downrange PCs now connect and function as advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get connection between local network
> apps and an SQLExpress database. It seems like a DBA would be required even for SQLExpress
> installs, which surely is not what Microsoft intended. What could I have done better or easier?
>|||"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
Using Express and using Workgroup Authentication each introduce one extra
step. Express requires remote connections to be enabled manually and
Workgroup Authentication requires the creation of the local accounts and the
manual synchronization of passwords.
Steps 4,5 and 6 are always required in SQL Server. You must add a login for
the users, grant database access for the login, and assign database role
memberships.
David|||I don't think you want to enable the Guest account as a default for all
users. There's no security in that.
I would create a Security Group on the PC1 SQL host computer, something like
AppDBUsers. Then add each of the user accounts that you want to have access
to the database to that group. If different users have different db
privileges create a separate security group for each privilege set or job
function or however you want to distinguish. Then add the individual user
accounts to the appropriate security groups.
In SQL Server, create a login for each security group(s) and give that login
access to the db. Don't make that login the db owner. Instead you have the
opportunity to specify exactly what permissions you want each login to have
on each db object. Or if you don't need that level of granularity, you can
make that login a db reader and a db writer, so they can read and write
everything. If you have stored procedures you need to set permissions for
those too.
Even if you only have a few users now, the indirection of using the security
groups makes it easier to know who has which permissions. And if you get a
new user, all you have to do is add that one user to the appropriate
group(s) and they will have the appropriate db access.
Paul Shapiro
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>|||Thanks to Tibor, David, and Paul. This certainly cleared up my
(mis)understanding of the issue.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>

How to configure SQLExpress on a workgroup

I recently deployed an app with SQLExpress2005 as the backend. Despite
testing it here on my dev network, this was my first opportunity to use it
in a workgroup environment. I had to really jump through some hoops to get
connectivity between the app and the SQLExpress database. I'm asking for any
advice or constructive criticism on the steps I took, particularly in
regards to whether it was unnecessary or exposed any security issues.
Scenario:
-- Four PCs (all XP Pro) in a workgroup configuration -- no server,
connectivity is through a router.
-- SQLExpress installed on one of the PCs with a server instance
"PC1\SQLExpress".
-- Database "AppDB" attached to "PC1\SQLExpress".
-- A .Net application is installed on all workgroup PCs. The app works "as
advertised" on the PC where there SQLExpress is installed, with connection
string security set to SSPI.
Could not get connection between the apps on the other PCs to "AppDB",
sooooo ...
On the SQLExpress PC:
1. Ensured firewall had ports 1433 and 1434 exposed.
2. Added ALL the local workgroup users and their passwords to the XP machine
(PC1).
3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
over TCP/IP only.
4. Added "PC1\GUEST to SQLExpress Logins.
On the downrange PCs, the app now reported, "Cannot open database "AppDB"
requested by login."
5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ ->
Properties -> UserMapping and checked Map "AppDB"
On the downrange PCs, the app now reported, "EXECUTE permission denied on
"sel_Employees". So it appears that the user mapping WAS required.
6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
Database Role Membership and set to db_owner.
The application on all downrange PCs now connect and function as advertised!
This sure strikes me as incredibly Rube Goldberg in order to get connection
between local network apps and an SQLExpress database. It seems like a DBA
would be required even for SQLExpress installs, which surely is not what
Microsoft intended. What could I have done better or easier?
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
Using Express and using Workgroup Authentication each introduce one extra
step. Express requires remote connections to be enabled manually and
Workgroup Authentication requires the creation of the local accounts and the
manual synchronization of passwords.
Steps 4,5 and 6 are always required in SQL Server. You must add a login for
the users, grant database access for the login, and assign database role
memberships.
David
|||I don't think you want to enable the Guest account as a default for all
users. There's no security in that.
I would create a Security Group on the PC1 SQL host computer, something like
AppDBUsers. Then add each of the user accounts that you want to have access
to the database to that group. If different users have different db
privileges create a separate security group for each privilege set or job
function or however you want to distinguish. Then add the individual user
accounts to the appropriate security groups.
In SQL Server, create a login for each security group(s) and give that login
access to the db. Don't make that login the db owner. Instead you have the
opportunity to specify exactly what permissions you want each login to have
on each db object. Or if you don't need that level of granularity, you can
make that login a db reader and a db writer, so they can read and write
everything. If you have stored procedures you need to set permissions for
those too.
Even if you only have a few users now, the indirection of using the security
groups makes it easier to know who has which permissions. And if you get a
new user, all you have to do is add that one user to the appropriate
group(s) and they will have the appropriate db access.
Paul Shapiro
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
|||Thanks to Tibor, David, and Paul. This certainly cleared up my
(mis)understanding of the issue.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>

How to configure SQLExpress on a workgroup

I recently deployed an app with SQLExpress2005 as the backend. Despite
testing it here on my dev network, this was my first opportunity to use it
in a workgroup environment. I had to really jump through some hoops to get
connectivity between the app and the SQLExpress database. I'm asking for any
advice or constructive criticism on the steps I took, particularly in
regards to whether it was unnecessary or exposed any security issues.
Scenario:
-- Four PCs (all XP Pro) in a workgroup configuration -- no server,
connectivity is through a router.
-- SQLExpress installed on one of the PCs with a server instance
"PC1\SQLExpress".
-- Database "AppDB" attached to "PC1\SQLExpress".
-- A .Net application is installed on all workgroup PCs. The app works "as
advertised" on the PC where there SQLExpress is installed, with connection
string security set to SSPI.
Could not get connection between the apps on the other PCs to "AppDB",
sooooo ...
On the SQLExpress PC:
1. Ensured firewall had ports 1433 and 1434 exposed.
2. Added ALL the local workgroup users and their passwords to the XP machine
(PC1).
3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
over TCP/IP only.
4. Added "PC1\GUEST to SQLExpress Logins.
On the downrange PCs, the app now reported, "Cannot open database "AppDB"
requested by login."
5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ ->
Properties -> UserMapping and checked Map "AppDB"
On the downrange PCs, the app now reported, "EXECUTE permission denied on
"sel_Employees". So it appears that the user mapping WAS required.
6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
Database Role Membership and set to db_owner.
The application on all downrange PCs now connect and function as advertised!
This sure strikes me as incredibly Rube Goldberg in order to get connection
between local network apps and an SQLExpress database. It seems like a DBA
would be required even for SQLExpress installs, which surely is not what
Microsoft intended. What could I have done better or easier?
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
Using Express and using Workgroup Authentication each introduce one extra
step. Express requires remote connections to be enabled manually and
Workgroup Authentication requires the creation of the local accounts and the
manual synchronization of passwords.
Steps 4,5 and 6 are always required in SQL Server. You must add a login for
the users, grant database access for the login, and assign database role
memberships.
David
|||I don't think you want to enable the Guest account as a default for all
users. There's no security in that.
I would create a Security Group on the PC1 SQL host computer, something like
AppDBUsers. Then add each of the user accounts that you want to have access
to the database to that group. If different users have different db
privileges create a separate security group for each privilege set or job
function or however you want to distinguish. Then add the individual user
accounts to the appropriate security groups.
In SQL Server, create a login for each security group(s) and give that login
access to the db. Don't make that login the db owner. Instead you have the
opportunity to specify exactly what permissions you want each login to have
on each db object. Or if you don't need that level of granularity, you can
make that login a db reader and a db writer, so they can read and write
everything. If you have stored procedures you need to set permissions for
those too.
Even if you only have a few users now, the indirection of using the security
groups makes it easier to know who has which permissions. And if you get a
new user, all you have to do is add that one user to the appropriate
group(s) and they will have the appropriate db access.
Paul Shapiro
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
|||Thanks to Tibor, David, and Paul. This certainly cleared up my
(mis)understanding of the issue.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>

how to configure from local distributor to remote

Hi,
I already have a merge replication setup successfully.
However I now need to change the network from a central publisher to a
central publisher with remote distributor
See http://www.databasejournal.com/features/mssql/article.php/1458491 and
Ctrl+F for the words central publisher.
I tried to redo another publication but the wizard no longer asks me whether
I want my distributor to be on a remote machine.
Then I tried to follow this article
http://technet.microsoft.com/en-us/library/ms151192.aspx
and looked for a Configure distribution which I cannot find.
Then I tried this article
http://msdn2.microsoft.com/en-us/library/ms147363.aspx
, but realise that the value of installed in the result set is 1, so I am
not sure what I should do next. Furthermore I am unfamiliar with the T-SQL
syntax necessary to make the changes.
FYI, the result set from using the sp_get_distribution was
installed = 1
distribution server = machineName
distribution db installed = 1
is distribution publisher = 1
has remote distribution = 0
Please advise. I am running SQL server 2005 Trial edition on 2 separate
win2003 server with one of them already set up as a successful central
publisher.
If I really have no choice but to use T-SQL please give me more explicit
details. Thank you.
This is pretty much a one-off choice. To move to a remote distributor I'd
script out the publications, remove the subscriptionas and publications,
remove the distributor entirely, readd the distributor remotely then add the
publications again.
HTH,
Paul Ibison