Is there a log of activity as compared to the Tran log?

I am trying to identify if I have unwanted guests gaining access to my
system or it's a run away query by a user on my network. I am thinking of
recording SPIDs as one way.
Via Spotlight I can see that I'm having beyond normal usage. Granted I have
a new PHB who likes to run queries off his laptop to show off to other
managers. Real scary when he has a little understanding of the data.
The box running Spotlight is WAY under powered, but can it keep that logging
data instead of the server itself?You can create your own log with profiler - it's a good tool
to track down performance issues in a database. If you use
it and performance is a concern, don't trace to the database
or from the database. Trace from a client and if tracing to
a file, have it go on that client.
Or use a server side trace. But you'd probably want to play
around with profiler from a client first.
-Sue
On Fri, 23 Sep 2005 08:30:52 -0500, "Stephen Russell"
<srussell@.transactiongraphics.com> wrote:

>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
>Via Spotlight I can see that I'm having beyond normal usage. Granted I hav
e
>a new PHB who likes to run queries off his laptop to show off to other
>managers. Real scary when he has a little understanding of the data.
>The box running Spotlight is WAY under powered, but can it keep that loggin
g
>data instead of the server itself?
>|||2 things here really
1. Tracking "unwanted guests" gaining access to your SQL Server is easily
tracked with no perf hit by setting "Audit level" to "All". This is strictly
for login attempts and can be se via EM. Right click on your server, select
properties and click on the security tab. This puts an entry in the logs
everytime a login attempt is successfully or failed. At the very minimum,
you should log failures.
2. Runaway queries are a little harder to do with SQL Server 2000. First,
you need to define what you mean by runaway queries. CPU consumption?
Memory? Length of query? SQL Profiler and a bunch of 3rd party tools from
folks like Quest, BMC, etc... let's you capture the appropriate data to
"look at" so you can figure out who's got a runaway query. Depending on what
you use, you may have to do extra work. For example, if you capture trace
info with Profiler or SQL Trace, you'll need a way to track the data and
raise alerts based on thresholds that you set. Life gets a LOT better in
2005 with the default "reports" (it's a cool dashboard really) but in 2000,
there's a little bit of work needed to get you there. :-)
Btw, one thing you might want to consider is the Query Governor cost limit
option. It's not the most "precise" tool you can have since it works based
on estimates but it's a pretty good start with minimal effort. Look it up in
BOL. It's well documented.
joe.
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:u5%23LlMEwFHA.664@.tk2msftngp13.phx.gbl...
>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
> Via Spotlight I can see that I'm having beyond normal usage. Granted I
> have a new PHB who likes to run queries off his laptop to show off to
> other managers. Real scary when he has a little understanding of the
> data.
> The box running Spotlight is WAY under powered, but can it keep that
> logging data instead of the server itself?
>
>

Is the SA login secure

When I login with an sa account to a SQL server on our network how is the
login and password transferred? Is it encrypted in someway or transferred in
plain text? If someone had a sniffer on my network could they see the
password just come across and grab it?It is encrypted but its very weak . If someone captured a network trace of
you logging in they would indeed be able to determine the password fairly
easily. If you want to see how easy it is, below is an example
--this is an example of a captured password packet
--actual password used was thequickbrownfox
/*
e2 a5 23 a5 f3 a5 b2 a5 f2 a5 33 a5 s.a...#. .....3.
000000B4 93 a5 13 a5 83 a5 82 a5 53 a5 d2 a5 43 a5 c3 a5 ...... S...C...
000000C4 53 a5 22 a5
*/
-- need to strip out the non password bytes
/*
e2 a5 23 a5 f3 a5 b2 a5 f2 a5 33 a5 93 a5 13 a5 83 a5 82 a5 53 a5 d2 a5 43
a5 c3 a5 53 a5 22 a5
*/
--form varbinary
declare @.packet varchar(500)
set @.packet = 'e2 a5 23 a5 f3 a5 b2 a5 f2 a5 33 a5 93 a5 13 a5 83 a5 82 a5
53 a5 d2 a5 43 a5 c3 a5 53 a5 22 a5'
set @.packet = '0x'+REPLACE(@.packet,' ','')
select @.packet
--decode
select
dbo. decoder(0xe2a523a5f3a5b2a5f2a533a593a513
a583a582a553a5d2a543a5c3a553a522
a5)
as password
You can see the code for the decoder function here
http://www.sqldbatips.com/presentat...HACKING_SQL.zip
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Buck Taylor" <BuckTaylor@.discussions.microsoft.com> wrote in message
news:491768E5-428E-4232-A6C9-6517326E3FA7@.microsoft.com...
> When I login with an sa account to a SQL server on our network how is the
> login and password transferred? Is it encrypted in someway or transferred
> in
> plain text? If someone had a sniffer on my network could they see the
> password just come across and grab it?|||To add to Jasper's comments, it really is best to NEVER use the sa username
and password. Make it two miles long, store it in a password vault, and
change it every 30 days just for fun. Use AD security to create an
administrative account the DBA uses. Change that password regularly as
well.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:Olkb7fg7EHA.128@.TK2MSFTNGP15.phx.gbl...
> It is encrypted but its very weak . If someone captured a network trace of
> you logging in they would indeed be able to determine the password fairly
> easily. If you want to see how easy it is, below is an example
> --this is an example of a captured password packet
> --actual password used was thequickbrownfox
> /*
> e2 a5 23 a5 f3 a5 b2 a5 f2 a5 33 a5 s.a...#. .....3.
> 000000B4 93 a5 13 a5 83 a5 82 a5 53 a5 d2 a5 43 a5 c3 a5 ...... S...C...
> 000000C4 53 a5 22 a5
> */
> -- need to strip out the non password bytes
> /*
> e2 a5 23 a5 f3 a5 b2 a5 f2 a5 33 a5 93 a5 13 a5 83 a5 82 a5 53 a5 d2 a5 43
> a5 c3 a5 53 a5 22 a5
> */
> --form varbinary
> declare @.packet varchar(500)
> set @.packet = 'e2 a5 23 a5 f3 a5 b2 a5 f2 a5 33 a5 93 a5 13 a5 83 a5 82 a5
> 53 a5 d2 a5 43 a5 c3 a5 53 a5 22 a5'
> set @.packet = '0x'+REPLACE(@.packet,' ','')
> select @.packet
> --decode
> select
>
dbo. decoder(0xe2a523a5f3a5b2a5f2a533a593a513
a583a582a553a5d2a543a5c3a553a522
a5)
> as password
> You can see the code for the decoder function here
> http://www.sqldbatips.com/presentat...HACKING_SQL.zip
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Buck Taylor" <BuckTaylor@.discussions.microsoft.com> wrote in message
> news:491768E5-428E-4232-A6C9-6517326E3FA7@.microsoft.com...
the[vbcol=seagreen]
transferred[vbcol=seagreen]
>

Is SQL required for 15 users

Hello:
I have 15 clients that connect to an Access database that is hosted on a sha
red network drive. We experienced data corruprion recently and the database
designer that was hired to fix this problem recommended that we introduce a
nd SQL server. Workstatio
ns are WinXP, with Office and Access 2000. Two clients are using Office 200
0, on Win2000Pro. The server is Win2000 with active directory.
This is a costly endeavour, and I wonder if it is an overkill for such a sma
ll site. A maximum of four users need to have write access simultanoeusly.
The database is less than 50Mb in size, and it is stored on a network dri
ve where all have full read
and write access. I would tend to reconfigure security appropriately on the
server and isolate the database and roll out ACCESS 2003 as a first solutio
n to resolve this issue, and then introduce SQL if problem persist. Does i
t make sense, or would you
recommend I roll-in a SQL server?
Thanks"CC" <anonymous@.discussions.microsoft.com> wrote in message
news:F2ED7F5C-8C6F-4FA5-99E6-A28116D5169D@.microsoft.com...
> Hello:
> I have 15 clients that connect to an Access database that is hosted on a
shared network drive. We experienced data corruprion recently and the
database designer that was hired to fix this problem recommended that we
introduce and SQL server. Workstations are WinXP, with Office and Access
2000. Two clients are using Office 2000, on Win2000Pro. The server is
Win2000 with active directory.
> This is a costly endeavour, and I wonder if it is an overkill for such a
small site. A maximum of four users need to have write access
simultanoeusly. The database is less than 50Mb in size, and it is stored
on a network drive where all have full read and write access. I would tend
to reconfigure security appropriately on the server and isolate the database
and roll out ACCESS 2003 as a first solution to resolve this issue, and then
introduce SQL if problem persist. Does it make sense, or would you
recommend I roll-in a SQL server?
> Thanks
>
Access 2003 has the same problems as Access 2000 with respect to corruption.
See if the developer can get MSDE. It's SqlServer, it's free and for a
small database with 15 clients it will work fine.
David|||A properly designed Access project can handle your description. It will by
definition be file-server and not client-server, but each user should have
their own front-end mdb and the back end should be at least one mdb. You
should seek help in nntp://comp.databases.ms-access.
hth
Eric
"CC" <anonymous@.discussions.microsoft.com> wrote in message
news:F2ED7F5C-8C6F-4FA5-99E6-A28116D5169D@.microsoft.com...
> Hello:
> I have 15 clients that connect to an Access database that is hosted on a
shared network drive. We experienced data corruprion recently and the
database designer that was hired to fix this problem recommended that we
introduce and SQL server. Workstations are WinXP, with Office and Access
2000. Two clients are using Office 2000, on Win2000Pro. The server is
Win2000 with active directory.
> This is a costly endeavour, and I wonder if it is an overkill for such a
small site. A maximum of four users need to have write access
simultanoeusly. The database is less than 50Mb in size, and it is stored
on a network drive where all have full read and write access. I would tend
to reconfigure security appropriately on the server and isolate the database
and roll out ACCESS 2003 as a first solution to resolve this issue, and then
introduce SQL if problem persist. Does it make sense, or would you
recommend I roll-in a SQL server?
> Thanks
>
>
>
>|||A properly designed Access DB may or may not be able to handle 15 users.
Probably yes. But we don't know the workload. We do know that they have been
having corruption problems which Access is susecptible to.
A single processor license of SQL Standard goes for less than 5K. MSDE of
course would be free if they can use that. Perhaps the MSDE version with
Access?
But even at 5K... I question whether or not it's truly a costly endeavor.
How much was paid to have the consultant come in and 'fix the problem'.
What's the cost to the business, including lost worker productivity, in
dealing with these corruption problems?
Access is a great DB. But Access doesn't scale as well and if customer is
already seeing corruption, perhaps they should consider SQL. I don't
honestly think cost shoudl be the deciding factor in a situation like this,
at least not unless the 'cost' of having corrputed Access db's in factored.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Eric Sabine" <mopar41@.___ho_y_tmail.ScPoAmM> wrote in message
news:eYY9g$tBEHA.712@.tk2msftngp13.phx.gbl...
> A properly designed Access project can handle your description. It will
by
> definition be file-server and not client-server, but each user should have
> their own front-end mdb and the back end should be at least one mdb. You
> should seek help in nntp://comp.databases.ms-access.
> hth
> Eric
>
> "CC" <anonymous@.discussions.microsoft.com> wrote in message
> news:F2ED7F5C-8C6F-4FA5-99E6-A28116D5169D@.microsoft.com...
> shared network drive. We experienced data corruprion recently and the
> database designer that was hired to fix this problem recommended that we
> introduce and SQL server. Workstations are WinXP, with Office and Access
> 2000. Two clients are using Office 2000, on Win2000Pro. The server is
> Win2000 with active directory.
> small site. A maximum of four users need to have write access
> simultanoeusly. The database is less than 50Mb in size, and it is stored
> on a network drive where all have full read and write access. I would
tend
> to reconfigure security appropriately on the server and isolate the
database
> and roll out ACCESS 2003 as a first solution to resolve this issue, and
then
> introduce SQL if problem persist. Does it make sense, or would you
> recommend I roll-in a SQL server?
>|||We experienced data corruption once since my arrival, but no maintenance was
performed on the databases at all for a few years. We were plagued with ne
twork slowdows at the time of the incident. Maintenance was performed on th
e server (among other thing
s that were found is 16% fragmentation). The contractor that created the
database invoice a day of work. I understand the problem was resolved by s
imply using compact and repair.
The database was created originally under PAradox, then migrated to ACCESS 9
7 and then to 2000. I suspect that this is the root of the problem, coupled
with the fact that we are using 2000 as front end and that there was no mai
ntenance.|||"cc" <anonymous@.discussions.microsoft.com> wrote in message
news:5293B9C9-9C65-4406-9659-954DF6BD5227@.microsoft.com...
> We experienced data corruption once since my arrival, but no maintenance
was performed on the databases >at all for a few years. We were plagued
with network slowdows at the time of the incident. Maintenance was
>performed on the server (among other things that were found is 16%
fragmentation). The contractor that >created the database invoice a day
of work. I understand the problem was resolved by simply using compact >and
repair.
> The database was created originally under PAradox, then migrated to ACCESS
97 and then to 2000. I >suspect that this is the root of the problem,
coupled with the fact that we are using 2000 as front end and that >there
was no maintenance.
Network problems will almost always cause database corruption in Access.
If your network problems are solved, your corruption problems may be solved
too.
David

Is replication without network connection but file-based possible?

Hello,

I've been looking around in several SQL-Forums and in SQL Books Online but haven't found clear suggestions how to tackle my problem:

Our company has to fill a database with documentation for our contractor. Our subcontractors have to supply information from their branch/craft into this database, too.

Replication (without determining a type at this time) would do fine for data-exchange between us and our subcontractors.

But for working in different companies the networks are not connected
and in some companies/departments connection to the Internet is prohibited at all.
This would result in data-exchange based on files - which wont be a problem as data needs to be updated/exchanged only once a week and is usually done at meetings using DVD-Rs.

scenario in short:
- two-way exchange of data between SQL servers being not connected
- update needed once a week
- our server: SQL Server 2005 Standard (preferred)
- servers of our subcontractors: SQL Server Express 2005 (preferred)

That's the point where I dont know how to proceed:
Is two-way replication based on files possible at all?

I read about Snapshots, but to my understanding this replaces the "whole database" without caring about changes made to it and that's why it wont work for two-way exchange.

I would appreciate your hints how to solve this problem in general or whether I should look out for other solutions - MS Access might work with restrictions - but I would prefer SQL Server.

Thanks for reading until here and in advance for your answers.

Regards,

Michael__H

Hi Michael.

I do not think there is an easy solution for your problem. There is no offline file based replication solution that will also do 2-way replication for you.

The closest you can get by is backup restore but that will overwrite your data on the other side.

However if you have even Internet connection, then you will be able to solve your problem by using Web Synchronization which is a new feature in SQL Server Merge replication for SQL Server 2005.

|||

Hello Mahesh,

thank you for your answer.

According to your reply I'm going to solve my problem by using stored procedures writing my own little "replication":

- select records to be altered by our subcontractors
- mark them in separate column as 'currently exported'
- copy to external database/file X
- let our subcontractors complete missing data in database/file X
- compare returned database/file X with records in database marked as 'currently exported'
- import changed records

Of course it won't be easy but as far as no other solution is possible...

Michael

|||

Michael,

Your solution will work but it could be tricky.

Again I encourage you to take a look at Web Synchronization if you remotely think that the subscribers could have atleast internet connection.

|||

Hello Mahesh,

I'll have a closer look at Web-Synchronization , it seems to be a solution. And if not, my "replication" will be used.

Your suggestion has given my a direction for further reading, thank you.

Michael

Is replication without network connection but file-based possible?

Hello,

I've been looking around in several SQL-Forums and in SQL Books Online but haven't found clear suggestions how to tackle my problem:

Our company has to fill a database with documentation for our contractor. Our subcontractors have to supply information from their branch/craft into this database, too.

Replication (without determining a type at this time) would do fine for data-exchange between us and our subcontractors.

But for working in different companies the networks are not connected
and in some companies/departments connection to the Internet is prohibited at all.
This would result in data-exchange based on files - which wont be a problem as data needs to be updated/exchanged only once a week and is usually done at meetings using DVD-Rs.

scenario in short:
- two-way exchange of data between SQL servers being not connected
- update needed once a week
- our server: SQL Server 2005 Standard (preferred)
- servers of our subcontractors: SQL Server Express 2005 (preferred)

That's the point where I dont know how to proceed:
Is two-way replication based on files possible at all?

I read about Snapshots, but to my understanding this replaces the "whole database" without caring about changes made to it and that's why it wont work for two-way exchange.

I would appreciate your hints how to solve this problem in general or whether I should look out for other solutions - MS Access might work with restrictions - but I would prefer SQL Server.

Thanks for reading until here and in advance for your answers.

Regards,

Michael__H

Hi Michael.

I do not think there is an easy solution for your problem. There is no offline file based replication solution that will also do 2-way replication for you.

The closest you can get by is backup restore but that will overwrite your data on the other side.

However if you have even Internet connection, then you will be able to solve your problem by using Web Synchronization which is a new feature in SQL Server Merge replication for SQL Server 2005.

|||

Hello Mahesh,

thank you for your answer.

According to your reply I'm going to solve my problem by using stored procedures writing my own little "replication":

- select records to be altered by our subcontractors
- mark them in separate column as 'currently exported'
- copy to external database/file X
- let our subcontractors complete missing data in database/file X
- compare returned database/file X with records in database marked as 'currently exported'
- import changed records

Of course it won't be easy but as far as no other solution is possible...

Michael

|||

Michael,

Your solution will work but it could be tricky.

Again I encourage you to take a look at Web Synchronization if you remotely think that the subscribers could have atleast internet connection.

|||

Hello Mahesh,

I'll have a closer look at Web-Synchronization , it seems to be a solution. And if not, my "replication" will be used.

Your suggestion has given my a direction for further reading, thank you.

Michael

Is Replication for Me? Help in understanding...

I have a situation where I need to distribute a db to 'subscribers' for use during network, and preferred application downtime. Currently, we do this by employing MS-Access. We update our db on the 'subscriber' by sending a text file with the new data using FTP.

When the 'subscriber' opens their local copy of the Access db application, a macro fires off to check for any new file in the ftproot folder, and if one is detect that is newer than the last update, it truncates the existing table, and imports the text file using a predefined import specification format. The process works well enough as is. However, we were hoping to move beyond our dependency on MS-Access for a variety of reasons, so we are looking at developing windows forms apps using the new Asp.Net v2.0 technology and Sql2005 and SqlExpress.

I need some clarification on how replication works. Does it allow a 'snapshot' db to be created on a subscriber that can be used when the network is down? If not do we have alternatives? For example, I guess we could export/import to the subscriber in some manner.

Hope I've made the case clear enough for some responses. Thanks in advance for your thoughts and help. :)

Here's a status update...

I was able to replicate a db from my publisher/distributor Sql2005 server to a subscriber running SqlExpress. The db on the subscriber is avaiable when the network is down.

Now, the issue I have to overcome is deploying a windows form app to the subscriber to be able to read the db on a subscriber machine. I'm having trouble figuring out the connection string. I can setup the application to read from the actual name of a subscriber/instance of SqlExpress, in this example, I'm running the subscriber db on on a server named 'myClient', and the SqlExpress instance name is 'SQLEXPRESS', using integrated security.

Data Source=myClient\SQLEXPRESS;Initial Catalog=SCIIS;Integrated Security=True

This allows the viewer app to connect to the local subscriber db, and fullfills 1 requirement, in that the viewer app can be used to view the mission critical info during network downtime or preferred application downtime.

However, since the connection string is 'hardwired' to the subscriber server name, I'd have to create as many different viewer apps as I have subscribers. That's not the way it should work. I really need to have this viewer app be able to connect to a default SqlExpress instance on whichever client subscriber server that needs it. Trouble is - I'm not sure how to set it up. I've tried using this connection string:

Data Source=localhost\SQLEXPRESS;Initial Catalog=SCIIS;Integrated Security=True

But, I get an error message:

An error has occurred while establishing a connection to the server. ... Named Pipes Provider, error: 40 - Could not open a connection to SQL Server.

Ah, well, that is where I am at this point. End of day, so I'll look forward seeing any advice from some folks who done this type of thing, and thanks for your help.

|||

Allright! Stayed over at work to try a couple of more times to crack the issue.

I, finally, hit upon the correct connection string to use in my application:

Data Source=127.0.0.1,1433;Initial Catalog=SCIIS;Integrated Security=True

The other piece of the puzzle was in configuring SqlExpress in the Protocols. I did have TCP/IP enabled, and looking at TCP/IP properties, on the TCP/ IP Properties tab, setup the proper IP addresses with port 1433 enabled. The trick that I missed was to blank out the TCP Dynamic Ports, and changing the IPAll area to use port 1433. Once I did that and restarted SqlExpress server, I was able to connect to the local subscriber database.

As a further test, I went to the subscriber machine and disabled network connections to simulate a network downtime scenario. I started my viewer app, and successfully connected to the local db, and was able to use the viewer app w/o any issue.

Next, obstacle... How to publish the app, so I can have it be a smart client, but that's not a Sql replication issue. So, I guess, I'll say my initial question was answered.

Stay tuned...

|||

Try to enable "Shared Memory" for your SQL server protocols to see if "localhost" will work for you. You can change this setting in SQL Server Configuration Manager.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5fff42e8-377f-4b40-b0c8-b02393f8a1af.htm

Regards,

Gary

|||

Gary,

I actually had all of the protocols enabled at the time that I tested using localhost, and it didn't seem to matter. The fix for me was to change to the IP, port, and making the change to the AllPorts section as indicated above. It's working now, and we're moving forward.

Thanks for the input, though.