Consolidate records

I consider security A to be equivalent to C so I want to consolidate these
positions. Three scenarios:
If an account holds both A and C I want to add the C quantity to the A
position and delete the C position.
If the account holds A but not C then no action.
If the account holds C but not A I want to update the C position to A.
My requirements are such that I need UPDATE and DELETE statement not a
SELECT statement so that my desired results would be produced by SELECT *
FROM #Positions
Any help would be appreciated.
CREATE TABLE [dbo].[#Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',20)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'B',25)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'C',25)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'A',15)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'B',5)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'B',5)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'C',10)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (4,'C',15)
Expected results
1,A,45
1,B,25
2,A,15
2,B,5
3,A,10
3,B,5
4,A,15Please explain NULL quantity to me; I understand >=0, but not a NULL.
Create a synonym table,then create a VIEW with the base name and totals
and use it.|||Terri
The following UPDATE/DELETE statemenst should help you out:
-- Update SecurityID A with the values for SecurityID C where the AccountID
is the same
UPDATE a
SET a.quantity = a.quantity + b.quantity
FROM #Positions a JOIN #Positions b
ON a.accountid = b.accountid
AND a.securityid = 'A'
AND b.securityid = 'C'
-- Delete any of the SecurityID C rows that were used in the previous update
DELETE b
FROM #Positions a JOIN #Positions b
ON a.accountid = b.accountid
AND a.securityid = 'A'
AND b.securityid = 'C'
-- Update SecurityID C to SecurityID A
UPDATE #Positions
SET securityid = 'A'
WHERE securityid = 'C'
- Peter Ward
WARDY IT Solutions
"Terri" wrote:

> I consider security A to be equivalent to C so I want to consolidate these
> positions. Three scenarios:
> If an account holds both A and C I want to add the C quantity to the A
> position and delete the C position.
> If the account holds A but not C then no action.
> If the account holds C but not A I want to update the C position to A.
> My requirements are such that I need UPDATE and DELETE statement not a
> SELECT statement so that my desired results would be produced by SELECT *
> FROM #Positions
> Any help would be appreciated.
> CREATE TABLE [dbo].[#Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',20)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'B',25)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'C',25)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'A',15)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'B',5)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'B',5)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'C',10)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (4,'C',15)
> Expected results
> 1,A,45
> 1,B,25
> 2,A,15
> 2,B,5
> 3,A,10
> 3,B,5
> 4,A,15
>
>sqlsql

Connectivity issue

I am using the connection string as

"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TMS_CHECK_CONVERSION;Data Source=PHILDCAP4Q\VENDOR" in

specified named instance is installed on the Respective machine.

It is giving the following error.,

Sqlserver does not exist or access denied.,

Please suggest ...?

Are you using Windows authentication on your SQL Server instance? If yes, is your SQL Server a member server in a domain?|||

Yes I am using windows authentication only also sql server is a member server in the domain.

still I am getting the same error.

Any help is appericated.

|||

Is this SQL Server 2000 or 2005? If it's SS2005, is your sql browser running? What are the protocols enabled on the server? Any firewall issue? You'd better follow the instruction here and give us more details. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||

Named instance of SQL server is installed on Windows 2003 server.

And we are trying to connect to .Net with the follwoing connection string.,

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>; Data Source= <Sql Server Installed Machine Name>\<Sql server instance Name>

we are getting the error as "SQL Server does not exist or access denied.

The information

[1] Client side:

What is the connection string in you app or DSN? (please specify)

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>; Data Source= <Sql Server Installed Machine Name>\<Sql server instance Name>

If client fails to connect, what is the client error messages? (please specify)

Sql Server does not exist or access denied

Is the client remote or local to the SQL server machine? [Remote | Local]

Remote

Can you ping your server? [YES | NO ]
In cmd.exe console, type “ping -a <server_name>”.

Yes.

Can you telnet to your SQL Server? [YES | NO, please specify the error message ]
In cmd.exe console, type “telnet <server name> port, where port can be 135, 445 or sql_server_tcp_port. If your cmd.exe console turns into a complete black screen with a cursor flushing on top left corner, you are connected. Type ctrl+’[‘ to bring up telnet prompt and type “quit” <enter>.

Yes

What is your client database provider? [SNAC | MDAC | ADO.NET1.0 | ADO.NET2.0| other (please specify] Or/And, what is your client application? [SQL Management Studio | SQL Profiler | Visual Studio | Other (please specify).

MDAC Ver 2.8

Is your client computer in the same domain as the Server computer? (Same domain | Different domains | WorkGroup)

Yes

What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes].

TCP/IP and Named Pipes

Do you have aliases configured that match the server name portion of your connection string? If so, please check if it is correct. You can use cliconfg.exe (SS 2000) or SQL Server Configuration Manager (SS 2005) to configure the alias.

No alias

Do you select force encryption on server and/or client?
No, theser are unchecked on both client server

[2] Server side:

What is the MS SQL version? [ SQL Server 2005 | SQL Sever 2005 ]

Sql server 2000 sp 4.

What is the SKU of MS SQL? [Enterprise | Standard | Workgroup | Express (or MSDE) | other (please specify)].

Enterprise

What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. Use SQL Server Configuration Manager to configure it and check ERRORLOG or event log to confirm.

TCP/IP and Named Pipes

Does the server start successfully? [YES | NO] If not what is the error messages in the SQL server ERRORLOG?

Yes.

If SQL Server is a named instance, is the SQL browser enabled? [YES | NO]

We are using sql server 2000, sp4.

What is the account that the SQL Server is running under?[Local System | Network Service | Domain Account]

Local system

Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES | NO | not applicable]

Don't know how to check this., Whether this is applicable for sql server 2000 also.,

Do you make firewall exception for SQL Browser UDP port 1434? [YES | NO | not applicable ]

Don't know how to check this., Whether this is applicable for sql server 2000 also.,

[3] Platform:

What is the OS version? [Windows XPSP2 | Windows 2003 | Windows 2000 | Windows 98 | others (please specify ) ].

Windows server 2003

Do you have third party antivirus, anti-spareware software installed? [Symantec | Norton | other (please specify)].

Yes. But I am not sure which is installed on customer's place.,

Thanks a lot.,

Any Help is really appriciated.

|||

Chugupta,

"Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES | NO | not applicable]

Don't know how to check this., Whether this is applicable for sql server 2000 also.,

Do you make firewall exception for SQL Browser UDP port 1434? [YES | NO | not applicable ]

Don't know how to check this., Whether this is applicable for sql server 2000 also.,
"

You need to check both for your SQL2000 server. For SQL2000, you still need to make sure that your tcp port (Use ERRORLOG to check the value of the TCP port that your server is listening on) and UDP 1434 port are not blocked by the firewall. Mostlikely, they are blocked by firewall setting.

The following KB can give you a clue.

http://support.microsoft.com/?kbid=914277&SD=tech

|||

The most astonish thing here is., we are able to connect through delphi code and we are doing database operations. But when we are trying to connect the sql server through .Net it is throwing an exception sql server does not exist or access denied.

|||

Can you post the exact connection string? I hope you are not using <>.

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>; Data Source= <Sql Server Installed Machine Name>\<Sql server instance Name>

|||

The connection string which I am using is

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTest; Data Source= ServerPC\TestInstance

DBTest is the database name

ServerPC is the system where the sql server is installed

TestInstance is the name instance of sql server on ServerPc

Sql server is installed on Windows 2003 Server.

Using the above conncetion string I am able to connect the sql server using Delphi but not with .Net.,

When I am trying to connect through VB.Net it is throwing an exception "SQL Server does not exist or access denied.

|||

Thanks for confirmation. I'm not sure how Delphi works, but can you make sure UDP port 1434 is not blocked? See the above post by Nan.

Connection to SQL Server

Hello,

I have a web application that queries data from a SQL Server. For develoment, I used integrated security to connect to the database. Now, I would like to put the application on a server and connect to the SQL Server using a username/password.

Here is my connection string

"data source=CHRISANNE;initial catalog=TAPTest;uid=TapWEBUser;pwd=myPassword"

When I attempt to connect to the SQL Server, I get the following error:
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'TapWEBUser'

The user profile was created and was added to the database with public, db_datareader, and db_datawriter permissions. I'm not sure what I am doing wrong. Does anyone have any suggestions? Thanks in advance!

TimCan you login to the database using that user name and password using Query Analyzer? I would try "User ID" rather than "uid". I do not see uid in the documentation.|||I attempted to login to the database using the Query Analyzer...no luck.

I recieved the following error.

Msg 18456, Level 16, State 1
Login failed for user 'TapWEBUser'

Also, I changed the uid to User ID....also no luck. Any more suggestions? Thanks.|||Well, then TapWEBUser is not set up correctly, or you are using the wrong password, or some combination. Until you can log into Query Analyzer, you have a SQL user problem unrelated to ASP.NET.

I would try and reset the users password and give it another shot.|||Ok...thanks for the advise. I found the problem. The dba gave me the wrong password.

Thanks for your help.

Connection to SQL Express ONLY with integrated security but WITHOUT domain

Does anybody know if it is possible to establish a connection to an sql express instance only with integrated security when this express instance is running on XP which is NOT part of a domain?

Thanks,

Klaus

If the SQL Express machine isn't connected to a domain then I don't believe Kerberos authentication will work but NTLM authentication should work. If the client application is running on the same machine as SQL Express then you should be able to straightforwardly give a user account local to the machine permission to connect to SQL Express (machine administrators will have it by default). If you're connecting from off-box then I think you'll need to setup the same account (same user name & password) on the remote machine.

Hope that helps.

Vaughn

Connection string security.

Howdy all.
Currently on 2000, but will be going to 2005 in the next couple of months.
We recently ran into a scenario where a developer built a back door into our
Production SQL box, and we are not too happy about it. Here is the scenario:
Since a lot of our apps are for the internet, we mostly use SQL Server
authentication for the app to talk to the DB. In our environment, developers
are NOT allowed to connect directly to our OLTP box, and as such dont have
the username or password for this connection. But one of them built in
funcationality to display the connection string (username and password
included) into his app. The DB connection string is stored in a web config
file, and he is simply querying that file. Is there anything I can do to
prevent this? Apps are written in both .NET and Java.
TIA, ChrisRIf your developer programmed some back door into the app, there's not much
you can do from that end, other than changing the app to use windows
authentication. You can do this without requiring users to log in
explicitly by setting the web site to anonymous authentication, but setting
the identity to a domain user that has been granted limited access to SQL
Server (ideally, the login will only have permissions to execute stored
procedures in certain database(s)).
However, what you should ensure is that the apps that talk to SQL Server do
so only through the internal network. The web app is visible to the world,
but the SQL Server box is not. You can use a mix of hardware/software
firewall appliances to accomplish this. If the developer is not inside your
network, then he/she cannot connect to SQL Server regardless of the
credentials they are trying to use.
A
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2D572DAA-2C92-46D9-8057-20C4B8A3304C@.microsoft.com...
> Howdy all.
> Currently on 2000, but will be going to 2005 in the next couple of months.
> We recently ran into a scenario where a developer built a back door into
> our
> Production SQL box, and we are not too happy about it. Here is the
> scenario:
> Since a lot of our apps are for the internet, we mostly use SQL Server
> authentication for the app to talk to the DB. In our environment,
> developers
> are NOT allowed to connect directly to our OLTP box, and as such dont have
> the username or password for this connection. But one of them built in
> funcationality to display the connection string (username and password
> included) into his app. The DB connection string is stored in a web config
> file, and he is simply querying that file. Is there anything I can do to
> prevent this? Apps are written in both .NET and Java.
> TIA, ChrisR|||Thanks Aaron!
"Aaron Bertrand [SQL Server MVP]" wrote:
> If your developer programmed some back door into the app, there's not much
> you can do from that end, other than changing the app to use windows
> authentication. You can do this without requiring users to log in
> explicitly by setting the web site to anonymous authentication, but setting
> the identity to a domain user that has been granted limited access to SQL
> Server (ideally, the login will only have permissions to execute stored
> procedures in certain database(s)).
> However, what you should ensure is that the apps that talk to SQL Server do
> so only through the internal network. The web app is visible to the world,
> but the SQL Server box is not. You can use a mix of hardware/software
> firewall appliances to accomplish this. If the developer is not inside your
> network, then he/she cannot connect to SQL Server regardless of the
> credentials they are trying to use.
> A
>
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2D572DAA-2C92-46D9-8057-20C4B8A3304C@.microsoft.com...
> > Howdy all.
> >
> > Currently on 2000, but will be going to 2005 in the next couple of months.
> > We recently ran into a scenario where a developer built a back door into
> > our
> > Production SQL box, and we are not too happy about it. Here is the
> > scenario:
> >
> > Since a lot of our apps are for the internet, we mostly use SQL Server
> > authentication for the app to talk to the DB. In our environment,
> > developers
> > are NOT allowed to connect directly to our OLTP box, and as such dont have
> > the username or password for this connection. But one of them built in
> > funcationality to display the connection string (username and password
> > included) into his app. The DB connection string is stored in a web config
> > file, and he is simply querying that file. Is there anything I can do to
> > prevent this? Apps are written in both .NET and Java.
> >
> > TIA, ChrisR
>
>

Connection string OLEDB - SqlServer

I need to conect my software from a remote client to a server, my
standard connection:

Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=MyDb;Data
Source=192.168.0.100;Network=DBMSSOCN;

and

Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=MyDb;Data
Source=192.168.0.100;Network=DBNMPNTW;

make a error connection,
i have tryed with ODBC connection and its OK only in Named Piped.

Can any one help me write the right connection string?

thanks
rmrmartin (rmartin@.freemail.it) writes:
> I need to conect my software from a remote client to a server, my
> standard connection:
> Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=MyDb;Data
> Source=192.168.0.100;Network=DBMSSOCN;
> and
> Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=MyDb;Data
> Source=192.168.0.100;Network=DBNMPNTW;
> make a error connection,
> i have tryed with ODBC connection and its OK only in Named Piped.
> Can any one help me write the right connection string?

Maybe. If you help us to help you. If you get any error message, please
post it. It would also help if you shared information about your network.

You talk about named pipes, but in the connection string you have an
IP address. I'm not good a networking, but it does not sound like a
good thing to me. In any case, I believe it is better to not specify
the network library at all, but control that from the Client Network
Utility.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"rmartin" <rmartin@.freemail.it> wrote in message
news:d7999283.0501261007.201e7769@.posting.google.c om...
>I need to conect my software from a remote client to a server, my
> standard connection:
> Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=MyDb;Data
> Source=192.168.0.100;Network=DBMSSOCN;
> and
> Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=MyDb;Data
> Source=192.168.0.100;Network=DBNMPNTW;
> make a error connection,
> i have tryed with ODBC connection and its OK only in Named Piped.
> Can any one help me write the right connection string?
> thanks
> rm

In addition to Erland's comments, you might find the sample connection
strings here useful:

http://www.able-consulting.com/ADO_Conn.htm

Simon

Connection security problems with a 3 tier application

Please bear with me as you read this description. I am trying to finish up
and productize some code that I inherited from a
contract programmer that allegedly knew what he was doing. I know that I do
n't know what I am doing.
I have an application that is written in C# for .net. It has a client porti
on, a server portion and a database back end. The
client connects to remote objects that are hosted in IIS on a server. These
objects connect the database on a 3rd system via
SQLDMO in .Net
Both the server and the SQL server systems are Windows 2003 systems.
The database is set for NT security only.
The server system is trusted for delegation. It also does impersonation.
We have a Domain Local security group (Win 2K style mixed mode Active Direct
ory) that contains users from both the AD group and a
NT 4 domain that has 2 way trust to/from the AD group.
I can access the databases on the SQL server from the client, but I am the o
nly person that can.
If someone in the NT 4 domain attempts to access, we get an error telling th
at NULL is not associated with the group.
.... Login failed for user "(null)". Reason: Not associated with secure S
QL Server connection.
If someone in the AD domain attempts to access, we get an error telling us t
hat login failed.
.... Login failed for user "domain\userid". The actual domain and userid
is listed.
I don't have any idea where this has gone wrong.
Any suggestions will be appreciated.
Thanks
---
Roy Chastain
KMSystems, Inc.Hi Roy:
The problem is not very clear.
I think we should first ping each other by IP to see if it work righ.
Then we should try to telnet each other by 1433 port.
But I guess the problem is caused by the domain or workgroup problem.
Best Wishes
Wei Ci Zhou|||You are right the problem is not very clear, because I don't know enough to
explain it correctly, however, there are some things
in my post that I though were more obvious then your response indicated.
There is connection.
1) - I can access the database
2) - The people that can not access the database are getting a response from
SQL. (Therefore connectivity)
Yes, it is a security issue, but I have no idea about how to resolve a secur
ity issue with SQL.
The database is supposed to be created with the domain group that we all bel
ong to being the owner of the database. I have not
been able to find that code in our application. I know it is there somewher
e, because the application does create the database.
Perhaps someone could tell me what to look for to find the database creation
and owner setup.
Thanks
On Fri, 26 Mar 2004 13:41:41 +0800, "Wei Ci Zhou" <weicizhou@.hotmail.com.dis
scuss> wrote:

>Hi Roy:
> The problem is not very clear.
> I think we should first ping each other by IP to see if it work righ.
> Then we should try to telnet each other by 1433 port.
> But I guess the problem is caused by the domain or workgroup problem.
>Best Wishes
>Wei Ci Zhou
>
---
Roy Chastain
KMSystems, Inc.|||Hi Roy,
As for the connectivity problem of SQL Server, there is some general steps
for us for testing.
1) ping Server_IP
2) telnet Server_IP 1433
Note that the Port 1433 is default por number fro SQL Server, You can check
the actual port number the SQL Server is using by Server Network Utility.
You can get it by check the properties of TCP/IP ( which should be enabled
protocol).
If now there is no problem of it. Then SQL Server is running properly and
you could use the Query Analyzer and ospl tools to connect from where you
need to connect the database with related credential informations. You
could also use the SQL Server Profiler to catch the connection information
from the outside. You could monitor the Audit: Login, Audit:Logout;
Sessions: ExistingConnections.
If no problem of it. Then the SQL Server is with the right configuration
for connectivity. It might be a IIS configuration problem, so, you could
ask for some information in the IIS newsgroup and our engineer there will
provide more
information on this issue.
http://msdn.microsoft.com/newsgroup...microsoft.publi
c.inetserver.iis
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Could you be so kind as to tell me how to use the SQL Server Profiler to mon
itor as you suggest.
On Mon, 29 Mar 2004 03:51:27 GMT, v-baiwei@.online.microsoft.com (Baisong Wei
[MSFT]) wrote:

>Hi Roy,
>
>As for the connectivity problem of SQL Server, there is some general steps
>for us for testing.
>1) ping Server_IP
>2) telnet Server_IP 1433
>Note that the Port 1433 is default por number fro SQL Server, You can check
> the actual port number the SQL Server is using by Server Network Utility.
>You can get it by check the properties of TCP/IP ( which should be enabled
>protocol).
>If now there is no problem of it. Then SQL Server is running properly and
>you could use the Query Analyzer and ospl tools to connect from where you
>need to connect the database with related credential informations. You
>could also use the SQL Server Profiler to catch the connection information
>from the outside. You could monitor the Audit: Login, Audit:Logout;
>Sessions: ExistingConnections.
>If no problem of it. Then the SQL Server is with the right configuration
>for connectivity. It might be a IIS configuration problem, so, you could
>ask for some information in the IIS newsgroup and our engineer there will
>provide more
>information on this issue.
>http://msdn.microsoft.com/newsgroup...ft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only. Thanks.
---
Roy Chastain
KMSystems, Inc.|||I have been doing some network snooping. I have code that identifies all SQ
L servers on the network. It does a broadcast on UDP
1434 and it get responses from several servers including the one in question
.
I have client code that will connect to the server in question and that code
will display the list of databases on the server.
Somehow the code in the client knows to use port 1175. This works. The cli
ent can query the database and that query is submitted
on port 1175 and it works.
I installed the SQL Web Administration tool today. Needless to say it does
not connect to the server in question. It will
connect to at least one other server on my network.
On Mon, 29 Mar 2004 03:51:27 GMT, v-baiwei@.online.microsoft.com (Baisong Wei
[MSFT]) wrote:

>Hi Roy,
>
>As for the connectivity problem of SQL Server, there is some general steps
>for us for testing.
>1) ping Server_IP
>2) telnet Server_IP 1433
>Note that the Port 1433 is default por number fro SQL Server, You can check
> the actual port number the SQL Server is using by Server Network Utility.
>You can get it by check the properties of TCP/IP ( which should be enabled
>protocol).
>If now there is no problem of it. Then SQL Server is running properly and
>you could use the Query Analyzer and ospl tools to connect from where you
>need to connect the database with related credential informations. You
>could also use the SQL Server Profiler to catch the connection information
>from the outside. You could monitor the Audit: Login, Audit:Logout;
>Sessions: ExistingConnections.
>If no problem of it. Then the SQL Server is with the right configuration
>for connectivity. It might be a IIS configuration problem, so, you could
>ask for some information in the IIS newsgroup and our engineer there will
>provide more
>information on this issue.
>http://msdn.microsoft.com/newsgroup...ft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only. Thanks.
---
Roy Chastain
KMSystems, Inc.|||Hi Roy,
As testing the connection to SQL Server, as you application will use the
specific credential information to connect to the SQL Server (windows
authentication or SQL Server authentication), you could use profiler to
catch this login information in Profiler:
When new a trace in the profiler, you could trace Audit Login and Audit
Login Failed in the Security Audit Event, and you will get all the
connection information of it.
You could refer to this article:
How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/?id=827422
Monitoring with SQL Profiler
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_mon_perf_86ib.asp
Hope this helps
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Roy,
I am reviewing your post and I provide you some links for connectivity to
SQL Server. I wonder if it is helpful or if you still have questions about
it. For this question, the first step we do is check that the credentials
used to connect the SQL server is valid. ( information are valid as SQL
logins). If yes, then the problem might be related to IIS configuration or
some Windows security policies. You could ask for help from:
http://msdn.microsoft.com/newsgroup...microsoft.publi
c.inetserver.iis
Hope this helps
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I gave up and opened a support incident yesterday. After 4 hrs on the phone
we thought it was resolved, but we were wrong.
On Thu, 01 Apr 2004 01:28:07 GMT, v-baiwei@.online.microsoft.com (Baisong Wei
[MSFT]) wrote:

>Hi Roy,
>I am reviewing your post and I provide you some links for connectivity to
>SQL Server. I wonder if it is helpful or if you still have questions about
>it. For this question, the first step we do is check that the credentials
>used to connect the SQL server is valid. ( information are valid as SQL
>logins). If yes, then the problem might be related to IIS configuration or
>some Windows security policies. You could ask for help from:
>http://msdn.microsoft.com/newsgroup...ft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only. Thanks.
---
Roy Chastain
KMSystems, Inc.

Connection security problems with a 3 tier application

Please bear with me as you read this description. I am trying to finish up and productize some code that I inherited from a
contract programmer that allegedly knew what he was doing. I know that I don't know what I am doing.
I have an application that is written in C# for .net. It has a client portion, a server portion and a database back end. The
client connects to remote objects that are hosted in IIS on a server. These objects connect the database on a 3rd system via
SQLDMO in .Net
Both the server and the SQL server systems are Windows 2003 systems.
The database is set for NT security only.
The server system is trusted for delegation. It also does impersonation.
We have a Domain Local security group (Win 2K style mixed mode Active Directory) that contains users from both the AD group and a
NT 4 domain that has 2 way trust to/from the AD group.
I can access the databases on the SQL server from the client, but I am the only person that can.
If someone in the NT 4 domain attempts to access, we get an error telling that NULL is not associated with the group.
..... Login failed for user "(null)". Reason: Not associated with secure SQL Server connection.
If someone in the AD domain attempts to access, we get an error telling us that login failed.
..... Login failed for user "domain\userid". The actual domain and userid is listed.
I don't have any idea where this has gone wrong.
Any suggestions will be appreciated.
Thanks
Roy Chastain
KMSystems, Inc.
Hi Roy:
The problem is not very clear.
I think we should first ping each other by IP to see if it work righ.
Then we should try to telnet each other by 1433 port.
But I guess the problem is caused by the domain or workgroup problem.
Best Wishes
Wei Ci Zhou
|||You are right the problem is not very clear, because I don't know enough to explain it correctly, however, there are some things
in my post that I though were more obvious then your response indicated.
There is connection.
1) - I can access the database
2) - The people that can not access the database are getting a response from SQL. (Therefore connectivity)
Yes, it is a security issue, but I have no idea about how to resolve a security issue with SQL.
The database is supposed to be created with the domain group that we all belong to being the owner of the database. I have not
been able to find that code in our application. I know it is there somewhere, because the application does create the database.
Perhaps someone could tell me what to look for to find the database creation and owner setup.
Thanks
On Fri, 26 Mar 2004 13:41:41 +0800, "Wei Ci Zhou" <weicizhou@.hotmail.com.disscuss> wrote:

>Hi Roy:
> The problem is not very clear.
> I think we should first ping each other by IP to see if it work righ.
> Then we should try to telnet each other by 1433 port.
> But I guess the problem is caused by the domain or workgroup problem.
>Best Wishes
>Wei Ci Zhou
>
Roy Chastain
KMSystems, Inc.
|||Hi Roy,
As for the connectivity problem of SQL Server, there is some general steps
for us for testing.
1) ping Server_IP
2) telnet Server_IP 1433
Note that the Port 1433 is default por number fro SQL Server, You can check
the actual port number the SQL Server is using by Server Network Utility.
You can get it by check the properties of TCP/IP ( which should be enabled
protocol).
If now there is no problem of it. Then SQL Server is running properly and
you could use the Query Analyzer and ospl tools to connect from where you
need to connect the database with related credential informations. You
could also use the SQL Server Profiler to catch the connection information
from the outside. You could monitor the Audit: Login, Audit:Logout;
Sessions: ExistingConnections.
If no problem of it. Then the SQL Server is with the right configuration
for connectivity. It might be a IIS configuration problem, so, you could
ask for some information in the IIS newsgroup and our engineer there will
provide more
information on this issue.
http://msdn.microsoft.com/newsgroups...icrosoft.publi
c.inetserver.iis
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
|||Could you be so kind as to tell me how to use the SQL Server Profiler to monitor as you suggest.
On Mon, 29 Mar 2004 03:51:27 GMT, v-baiwei@.online.microsoft.com (Baisong Wei[MSFT]) wrote:

>Hi Roy,
>
>As for the connectivity problem of SQL Server, there is some general steps
>for us for testing.
>1) ping Server_IP
>2) telnet Server_IP 1433
>Note that the Port 1433 is default por number fro SQL Server, You can check
> the actual port number the SQL Server is using by Server Network Utility.
>You can get it by check the properties of TCP/IP ( which should be enabled
>protocol).
>If now there is no problem of it. Then SQL Server is running properly and
>you could use the Query Analyzer and ospl tools to connect from where you
>need to connect the database with related credential informations. You
>could also use the SQL Server Profiler to catch the connection information
>from the outside. You could monitor the Audit: Login, Audit:Logout;
>Sessions: ExistingConnections.
>If no problem of it. Then the SQL Server is with the right configuration
>for connectivity. It might be a IIS configuration problem, so, you could
>ask for some information in the IIS newsgroup and our engineer there will
>provide more
>information on this issue.
>http://msdn.microsoft.com/newsgroups...icrosoft.publi
>c.inetserver.iis
>Hope this helps.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only. Thanks.
Roy Chastain
KMSystems, Inc.
|||I have been doing some network snooping. I have code that identifies all SQL servers on the network. It does a broadcast on UDP
1434 and it get responses from several servers including the one in question.
I have client code that will connect to the server in question and that code will display the list of databases on the server.
Somehow the code in the client knows to use port 1175. This works. The client can query the database and that query is submitted
on port 1175 and it works.
I installed the SQL Web Administration tool today. Needless to say it does not connect to the server in question. It will
connect to at least one other server on my network.
On Mon, 29 Mar 2004 03:51:27 GMT, v-baiwei@.online.microsoft.com (Baisong Wei[MSFT]) wrote:

>Hi Roy,
>
>As for the connectivity problem of SQL Server, there is some general steps
>for us for testing.
>1) ping Server_IP
>2) telnet Server_IP 1433
>Note that the Port 1433 is default por number fro SQL Server, You can check
> the actual port number the SQL Server is using by Server Network Utility.
>You can get it by check the properties of TCP/IP ( which should be enabled
>protocol).
>If now there is no problem of it. Then SQL Server is running properly and
>you could use the Query Analyzer and ospl tools to connect from where you
>need to connect the database with related credential informations. You
>could also use the SQL Server Profiler to catch the connection information
>from the outside. You could monitor the Audit: Login, Audit:Logout;
>Sessions: ExistingConnections.
>If no problem of it. Then the SQL Server is with the right configuration
>for connectivity. It might be a IIS configuration problem, so, you could
>ask for some information in the IIS newsgroup and our engineer there will
>provide more
>information on this issue.
>http://msdn.microsoft.com/newsgroups...icrosoft.publi
>c.inetserver.iis
>Hope this helps.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only. Thanks.
Roy Chastain
KMSystems, Inc.
|||Hi Roy,
As testing the connection to SQL Server, as you application will use the
specific credential information to connect to the SQL Server (windows
authentication or SQL Server authentication), you could use profiler to
catch this login information in Profiler:
When new a trace in the profiler, you could trace Audit Login and Audit
Login Failed in the Security Audit Event, and you will get all the
connection information of it.
You could refer to this article:
How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/?id=827422
Monitoring with SQL Profiler
http://msdn.microsoft.com/library/de...us/adminsql/ad
_mon_perf_86ib.asp
Hope this helps
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
|||Hi Roy,
I am reviewing your post and I provide you some links for connectivity to
SQL Server. I wonder if it is helpful or if you still have questions about
it. For this question, the first step we do is check that the credentials
used to connect the SQL server is valid. ( information are valid as SQL
logins). If yes, then the problem might be related to IIS configuration or
some Windows security policies. You could ask for help from:
http://msdn.microsoft.com/newsgroups...icrosoft.publi
c.inetserver.iis
Hope this helps
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
|||I gave up and opened a support incident yesterday. After 4 hrs on the phone we thought it was resolved, but we were wrong.
On Thu, 01 Apr 2004 01:28:07 GMT, v-baiwei@.online.microsoft.com (Baisong Wei[MSFT]) wrote:

>Hi Roy,
>I am reviewing your post and I provide you some links for connectivity to
>SQL Server. I wonder if it is helpful or if you still have questions about
>it. For this question, the first step we do is check that the credentials
>used to connect the SQL server is valid. ( information are valid as SQL
>logins). If yes, then the problem might be related to IIS configuration or
>some Windows security policies. You could ask for help from:
>http://msdn.microsoft.com/newsgroups...icrosoft.publi
>c.inetserver.iis
>Hope this helps
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only. Thanks.
Roy Chastain
KMSystems, Inc.