"Multiple IP address in one SQL server" and "authentication problem

Hi all.
I really don't known where to post because I don't known what is my
problem, so let me explain.
I have a SQL Server with two ethernet cards, one used for me, IT
Department, and other used by normal users.
Every card is in a separated network (obviously):
192.168.1.100 with "db.domain.com" A record in DNS
172.16.10.100 with "db.public.domain.com" A record in DNS
This server is an AD domain computer with "db.domain.com" name, and the
DC is in 192.168.1.0/24 network.
I am in troubles when I try to connect with MSrSQL Management Studio
using db.public.domain.com... I doesn't works, I get an error similar to
"Login failed for user ''. The user is not associated with a trusted SQL
Server connection. [CLIENT: 172.16.10.101]"
If I change db.public.domain.com to a CNAME record pointing to
db.domain.com it works, but this is not desired because I want users to
use their network, 172.16.10.0/24.
Any issues? Any suggestions? Any idea? Please help me if you can :) I
tried to play with Service Principal Names (SPN's), but I can't manage
to connect with an A record.
Thanks in advance,
SergioNever use a multihomed server as a DC. So you need to either remove AD off
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can :) I
> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio|||Sorry, I didn't explained well... it is not a DC, it is only a domain
member.
Paul Bergson [MVP-DS] wrote:
> Never use a multihomed server as a DC. So you need to either remove AD off
> of this machine or remove one of the NIC's.
> The link below has some info on this and I couldn't find the exact article
> but I can guarantee you are going to have constant problems with this setup.
> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
>|||Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
>> Never use a multihomed server as a DC. So you need to either remove AD
>> off of this machine or remove one of the NIC's.
>> The link below has some info on this and I couldn't find the exact
>> article but I can guarantee you are going to have constant problems with
>> this setup.
>> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>> Sorry for the misunderstanding. I don't specifically see what the
>> problem is, but does this sql server allow SQL Server and AD
>> authentication? If so have you tried both methods? What about the Event
>> Log what is the error you are receiving?

"Lost Inserts"

Is there any known SQL Server bug whereby a record can be successfully
inserted and committed, but then later be found not to be in the
database? For example, if there was a server crash just after the
commit, could committed data be lost?

I'm sure the answer must be "no", but a client is telling me this is
happening, and I said I'd enquire.Tony (andrewst@.onetel.net.uk) writes:
> Is there any known SQL Server bug whereby a record can be successfully
> inserted and committed, but then later be found not to be in the
> database? For example, if there was a server crash just after the
> commit, could committed data be lost?
> I'm sure the answer must be "no", but a client is telling me this is
> happening, and I said I'd enquire.

Of course, in case of a crash or a hardware error, the database could
become corrupt, in which case you could lose data. But in such cases
you should run DBCC CHECKDB to investigate.

If data appears to be lost, but the database appears to be safe and sound,
there are two possibilities:
1) Data was never committed, but was believed to because of an application
error. (There are a few gotchas that could lead to this.)
2) There is some other process that deletes the rows for one reason or
another.

In case of the latter, Lumgients Log Explorer (www.lumigent.com) can
be invaluable tool to track down what is happening.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tony" <andrewst@.onetel.net.uk> wrote in message
news:c0e3f26e.0311250510.d1d9de3@.posting.google.co m...
> Is there any known SQL Server bug whereby a record can be successfully
> inserted and committed, but then later be found not to be in the
> database? For example, if there was a server crash just after the
> commit, could committed data be lost?

Very unlikely, but possible.

> I'm sure the answer must be "no", but a client is telling me this is
> happening, and I said I'd enquire.

What makes them think this is happening?|||> If data appears to be lost, but the database appears to be safe and
sound,
> there are two possibilities:
> 1) Data was never committed, but was believed to because > of an
application error. (There are a few gotchas that could lead to this.)
> 2) There is some other process that deletes the rows for one reason or
another.

Yes, my suspicion is that it is possibility (1). The application is
written in ASP using ODBC to connect to the database. Autocommit is
used. The logic is something like this:
1) Insert record into table
2) Redirect to another page to print out a certificate

Allegedly, there are a few people holding certificates where no
corresponding record exists in the database. There is no code in the
app. that deletes this data, though of course it could be deleted
manually.

I have verified that if the insert fails (e.g. if I add a check
constraint that will always be violated), the ASP page aborts and does
not redirect to the next page.

Are there any particular scenarios I should look out for?

Thanks for your quick response.

Tony

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tony Andrews (andrewst@.onetel.net.uk) writes:
> Yes, my suspicion is that it is possibility (1). The application is
> written in ASP using ODBC to connect to the database. Autocommit is
> used. The logic is something like this:
> 1) Insert record into table
> 2) Redirect to another page to print out a certificate
> Allegedly, there are a few people holding certificates where no
> corresponding record exists in the database. There is no code in the
> app. that deletes this data, though of course it could be deleted
> manually.
> I have verified that if the insert fails (e.g. if I add a check
> constraint that will always be violated), the ASP page aborts and does
> not redirect to the next page.
> Are there any particular scenarios I should look out for?

There are a few nasty situations, which must be handled properly in
application code.

Say that you call a stored procedure which starts a user-defined
transaction. That stored procedure runs for a longer time, because of
the query or because of blocking. By default, all client libraries
have a timeout which sets in after 30 seconds (if no data have been
seen), and cancels the query. Contrary to what you may think, this DOES
NOT rollback the transaction! Thus, in your error-handling code you
need to issue "IF @.@.trancount > 0 ROLLBACK TRANSACTION".

Note here that even you don't use stored procedures but issue plain
INSERT statements, this can happen if there is a trigger on the table.
A trigger alwyas executes in the context of a transaction. (You may
get an automatic rollback when you cancel execution in a trigger; I
have not tested this case, but I would not trust on getting a ROLLBACK.)

There are some variations of this theme. If a stored procedure refers
to a non-existing table, the procedure will abort on that statement,
without rollback any transactions it may have started. Again, the
client could as a matter of routine always issue rollback in case of
an error.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp