SQL compact edition: February 2012

Saturday, February 25, 2012

Please Help: SQL service cannot start

Hi
I created an account to run MS SQL services and give logon
locally and log on as service permission. Installation was
successful with this service account.
After a few hours later, SQL Agent service hang and cannot
restart. Error message is applicaion fail to initialize.
Therefore MS SQL server service also cannot restart.
When I use LocalSystem to start SQL services, everything
is fine.But I need to use service account because of
security reasons.
When I login with this service account, it immedialy
logout. I cannot login at all.
MS SQL 2000 standard edition with SP3.
Any suggestion is appreciated.
ThanksHi,
Add the user to "power users" group and try.
Thanks
Hari
MCDBA
"Ben" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
> Hi
> I created an account to run MS SQL services and give logon
> locally and log on as service permission. Installation was
> successful with this service account.
> After a few hours later, SQL Agent service hang and cannot
> restart. Error message is applicaion fail to initialize.
> Therefore MS SQL server service also cannot restart.
> When I use LocalSystem to start SQL services, everything
> is fine.But I need to use service account because of
> security reasons.
> When I login with this service account, it immedialy
> logout. I cannot login at all.
> MS SQL 2000 standard edition with SP3.
> Any suggestion is appreciated.
> Thanks
>|||If when you login using thi sacount your session is terminated there is =clearly something wrong withthe account in some way. have you checked =the event log after your failed loginattempt - any error message =displayed?
Mike john
"Ben" <anonymous@.discussions.microsoft.com> wrote in message =news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
> Hi
> > I created an account to run MS SQL services and give logon > locally and log on as service permission. Installation was > successful with this service account.
> > After a few hours later, SQL Agent service hang and cannot > restart. Error message is applicaion fail to initialize.
> Therefore MS SQL server service also cannot restart.
> When I use LocalSystem to start SQL services, everything > is fine.But I need to use service account because of > security reasons.
> > When I login with this service account, it immedialy > logout. I cannot login at all. > > MS SQL 2000 standard edition with SP3.
> > Any suggestion is appreciated.
> > Thanks
> >|||Hi ben,
If i understand properly, this is what you need... please
refer the article -
http://support.microsoft.com/default.aspx?kbid=283811
it clearly says what permissions u need for the os level
id to start sqlserver.
but to have a short cut - please have an id in the
administrator's group to start mssql server if u do not
want to start with localsystem
regards,
bharath
mcdba
>--Original Message--
>Hi,
>Add the user to "power users" group and try.
>Thanks
>Hari
>MCDBA
>
>"Ben" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
>> Hi
>> I created an account to run MS SQL services and give
logon
>> locally and log on as service permission. Installation
was
>> successful with this service account.
>> After a few hours later, SQL Agent service hang and
cannot
>> restart. Error message is applicaion fail to initialize.
>> Therefore MS SQL server service also cannot restart.
>> When I use LocalSystem to start SQL services, everything
>> is fine.But I need to use service account because of
>> security reasons.
>> When I login with this service account, it immedialy
>> logout. I cannot login at all.
>> MS SQL 2000 standard edition with SP3.
>> Any suggestion is appreciated.
>> Thanks
>>
>
>.
>

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
--
Regards,
Anthony LamJust a thought...
Could it be an autogrow kicking in, which leads to the load wait?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program several
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > Dear Sir,
> > I have a program to write 30000 record to the database every minute
> > during daily operation. The program complete to save 30000 records in 30
> > sconds under normal sitaution. At night, all store data will be backup
to
> > another table and truncate the table. But after running the program
several
> > days, I find that the program need take about 3-5 minutes to save 30000
> > records completely even the table is empty. Actaully, all external
> > sitautions is unchanged such as number of user to access the table, the
> > amount of read data...etc. The performance is down so much. Then I
need
> > shut down the SQK Server Service and then start it again. Then , it will
> > fine again.
> > WHy? How can I make sure that the saving can finish withthin 1 minute?
> >
> > --
> > Regards,
> >
> > Anthony Lam
> >
> >
>|||So that could potentially be the reason. Growing a file takes time, and new inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> > Just a thought...
> >
> > Could it be an autogrow kicking in, which leads to the load wait?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "AA" <anthony@.jadeflex.com> wrote in message
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > Dear Sir,
> > > I have a program to write 30000 record to the database every minute
> > > during daily operation. The program complete to save 30000 records in 30
> > > sconds under normal sitaution. At night, all store data will be backup
> to
> > > another table and truncate the table. But after running the program
> several
> > > days, I find that the program need take about 3-5 minutes to save 30000
> > > records completely even the table is empty. Actaully, all external
> > > sitautions is unchanged such as number of user to access the table, the
> > > amount of read data...etc. The performance is down so much. Then I
> need
> > > shut down the SQK Server Service and then start it again. Then , it will
> > > fine again.
> > > WHy? How can I make sure that the saving can finish withthin 1 minute?
> > >
> > > --
> > > Regards,
> > >
> > > Anthony Lam
> > >
> > >
> >
> >
>

Please help: SQL Server Saving performance problem.

Please Help: Someone is hacking my server!

Hi,
My SQL server is has a public IP address admins and developers can access
and manage remotely (this is the requirement)
When I monitor the SQL server port I can see some one is brutally trying to
hack my server. There are almost 70 connections from IP address
66.15.173.105 that try to connect to my server.
My guess is he is trying to use brute force technique to find the sa
password.
What is the best way to prevent that? Can I adjust some settings, to have
failed logins wait for 10 seconds?
Any help would be appreciated,
Alan
Hi
You can't do much as you exposed your SQL Server to the outside world.
At best, you can filter the source IP and port at the router, blocking
66.15.173.105
SQL Server 2000 does not allow account lockouts or account policies.
If developers and admins need access to your SQL Server, look at
implementing a VPN, but for sure, don't expose SQL Server directly to the
internet.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>
|||Change the port from 1433, block that IP address, make the sa password
something ridiculously difficult (or better yet, switch to Windows
integrated security only) and implement VPN.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>
|||You can also use IPSEC & or your firewall and only allow specific IP & PORT
access to your SQL Box.
|||Blocking the attacking IP probably won't help -- it's probably a dynamic IP.
This might be painful, but...create a new admin. login with a hard-to-guess
name, use a strong password, and DELETE sa.
-- Jeff
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>
|||can not delete sa.
"Beige Bond" <BeigeBond@.hotmail.com> wrote in message
news:OvFRYk0TFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Blocking the attacking IP probably won't help -- it's probably a dynamic
IP.
> This might be painful, but...create a new admin. login with a
hard-to-guess[vbcol=seagreen]
> name, use a strong password, and DELETE sa.
> -- Jeff
> "A.M" <Hate-Spam@.nowhere.com> wrote in message
> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
access[vbcol=seagreen]
have
>
|||Apologies for my ignorance. Looks like turning off Mixed Mode Authentication
is the only way to disable sa. Some interesting notes at:
http://msdn.microsoft.com/library/de...erver_5un8.asp
"Dennis Redfield" <dennis_redfield@.newsgroup.nospam> wrote in message
news:eGetLtBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> can not delete sa.
> "Beige Bond" <BeigeBond@.hotmail.com> wrote in message
> news:OvFRYk0TFHA.2556@.TK2MSFTNGP12.phx.gbl...
> IP.
> hard-to-guess
> access
> have
>
|||In addition to all of these suggestions, you can also use the firewall
to block access from all outside IPs except those used by the admins
and developers.
Still the solution is to get a VPN setup and get the SQL Server box
off the internet. To expensive you say? How much will it cost your
company *when* the hacker gets through? I bet it's more than the cost
of some VPNs.
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Sat, 30 Apr 2005 11:39:51 -0400, "Michael C#" <xyz@.abcdef.com>
wrote:

>Change the port from 1433, block that IP address, make the sa password
>something ridiculously difficult (or better yet, switch to Windows
>integrated security only) and implement VPN.
>"A.M" <Hate-Spam@.nowhere.com> wrote in message
>news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>
|||Thank you everyone for your help and suggestions. I convinced development
team to use terminal services instead.
However, lack of proper password policy for SQL server is quite scary! It is
easy build a try/error program to hack sa password based on available
password dictionary databases!
Thanks again,
Alan
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>
|||That's probably why Windows Integrated is the recommended security model.
Why duplicate all of Windows' password policy functionality in SQL Server
when it's already accessible via Integrated Security?
Are you exposing Terminal Services to the Internet as well, or are you
making them use VPN? Don't forget the Administrator account on your domain
and local machines, which can usually use Terminal Services to log in as
well.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:%23YGjqxOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
>
> Thank you everyone for your help and suggestions. I convinced development
> team to use terminal services instead.
>
> However, lack of proper password policy for SQL server is quite scary! It
> is easy build a try/error program to hack sa password based on available
> password dictionary databases!
>
> Thanks again,
> Alan
>
> "A.M" <Hate-Spam@.nowhere.com> wrote in message
> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>

Please Help: Someone is hacking my server!

Hi,
My SQL server is has a public IP address admins and developers can access
and manage remotely (this is the requirement)
When I monitor the SQL server port I can see some one is brutally trying to
hack my server. There are almost 70 connections from IP address
66.15.173.105 that try to connect to my server.
My guess is he is trying to use brute force technique to find the sa
password.
What is the best way to prevent that? Can I adjust some settings, to have
failed logins wait for 10 seconds?
Any help would be appreciated,
AlanHi
You can't do much as you exposed your SQL Server to the outside world.
At best, you can filter the source IP and port at the router, blocking
66.15.173.105
SQL Server 2000 does not allow account lockouts or account policies.
If developers and admins need access to your SQL Server, look at
implementing a VPN, but for sure, don't expose SQL Server directly to the
internet.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||Change the port from 1433, block that IP address, make the sa password
something ridiculously difficult (or better yet, switch to Windows
integrated security only) and implement VPN.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||You can also use IPSEC & or your firewall and only allow specific IP & PORT
access to your SQL Box.|||Blocking the attacking IP probably won't help -- it's probably a dynamic IP.
This might be painful, but...create a new admin. login with a hard-to-guess
name, use a strong password, and DELETE sa.
-- Jeff
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||can not delete sa.
"Beige Bond" <BeigeBond@.hotmail.com> wrote in message
news:OvFRYk0TFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Blocking the attacking IP probably won't help -- it's probably a dynamic
IP.
> This might be painful, but...create a new admin. login with a
hard-to-guess
> name, use a strong password, and DELETE sa.
> -- Jeff
> "A.M" <Hate-Spam@.nowhere.com> wrote in message
> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
access[vbcol=seagreen]
have[vbcol=seagreen]
>|||Apologies for my ignorance. Looks like turning off Mixed Mode Authentication
is the only way to disable sa. Some interesting notes at:
ver_5un8.asp" target="_blank">http://msdn.microsoft.com/library/d...>
ver_5un8.asp
"Dennis Redfield" <dennis_redfield@.newsgroup.nospam> wrote in message
news:eGetLtBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> can not delete sa.
> "Beige Bond" <BeigeBond@.hotmail.com> wrote in message
> news:OvFRYk0TFHA.2556@.TK2MSFTNGP12.phx.gbl...
> IP.
> hard-to-guess
> access
> have
>|||In addition to all of these suggestions, you can also use the firewall
to block access from all outside IPs except those used by the admins
and developers.
Still the solution is to get a VPN setup and get the SQL Server box
off the internet. To expensive you say? How much will it cost your
company *when* the hacker gets through? I bet it's more than the cost
of some VPNs.
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Sat, 30 Apr 2005 11:39:51 -0400, "Michael C#" <xyz@.abcdef.com>
wrote:

>Change the port from 1433, block that IP address, make the sa password
>something ridiculously difficult (or better yet, switch to Windows
>integrated security only) and implement VPN.
>"A.M" <Hate-Spam@.nowhere.com> wrote in message
>news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>|||Thank you everyone for your help and suggestions. I convinced development
team to use terminal services instead.
However, lack of proper password policy for SQL server is quite scary! It is
easy build a try/error program to hack sa password based on available
password dictionary databases!
Thanks again,
Alan
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||That's probably why Windows Integrated is the recommended security model.
Why duplicate all of Windows' password policy functionality in SQL Server
when it's already accessible via Integrated Security?
Are you exposing Terminal Services to the Internet as well, or are you
making them use VPN? Don't forget the Administrator account on your domain
and local machines, which can usually use Terminal Services to log in as
well.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:%23YGjqxOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
>
> Thank you everyone for your help and suggestions. I convinced development
> team to use terminal services instead.
>
> However, lack of proper password policy for SQL server is quite scary! It
> is easy build a try/error program to hack sa password based on available
> password dictionary databases!
>
> Thanks again,
> Alan
>
> "A.M" <Hate-Spam@.nowhere.com> wrote in message
> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>

Please Help: Someone is hacking my server!

Hi,
My SQL server is has a public IP address admins and developers can access
and manage remotely (this is the requirement)
When I monitor the SQL server port I can see some one is brutally trying to
hack my server. There are almost 70 connections from IP address
66.15.173.105 that try to connect to my server.
My guess is he is trying to use brute force technique to find the sa
password.
What is the best way to prevent that? Can I adjust some settings, to have
failed logins wait for 10 seconds?
Any help would be appreciated,
AlanHi
You can't do much as you exposed your SQL Server to the outside world.
At best, you can filter the source IP and port at the router, blocking
66.15.173.105
SQL Server 2000 does not allow account lockouts or account policies.
If developers and admins need access to your SQL Server, look at
implementing a VPN, but for sure, don't expose SQL Server directly to the
internet.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||Change the port from 1433, block that IP address, make the sa password
something ridiculously difficult (or better yet, switch to Windows
integrated security only) and implement VPN.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||You can also use IPSEC & or your firewall and only allow specific IP & PORT
access to your SQL Box.|||Blocking the attacking IP probably won't help -- it's probably a dynamic IP.
This might be painful, but...create a new admin. login with a hard-to-guess
name, use a strong password, and DELETE sa.
-- Jeff
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||can not delete sa.
"Beige Bond" <BeigeBond@.hotmail.com> wrote in message
news:OvFRYk0TFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Blocking the attacking IP probably won't help -- it's probably a dynamic
IP.
> This might be painful, but...create a new admin. login with a
hard-to-guess
> name, use a strong password, and DELETE sa.
> -- Jeff
> "A.M" <Hate-Spam@.nowhere.com> wrote in message
> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> >
> >
> > My SQL server is has a public IP address admins and developers can
access
> > and manage remotely (this is the requirement)
> >
> >
> >
> > When I monitor the SQL server port I can see some one is brutally trying
> > to hack my server. There are almost 70 connections from IP address
> > 66.15.173.105 that try to connect to my server.
> >
> >
> >
> > My guess is he is trying to use brute force technique to find the sa
> > password.
> >
> >
> >
> > What is the best way to prevent that? Can I adjust some settings, to
have
> > failed logins wait for 10 seconds?
> >
> >
> >
> > Any help would be appreciated,
> >
> > Alan
> >
> >
> >
> >
> >
> >
>|||Apologies for my ignorance. Looks like turning off Mixed Mode Authentication
is the only way to disable sa. Some interesting notes at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_5un8.asp
"Dennis Redfield" <dennis_redfield@.newsgroup.nospam> wrote in message
news:eGetLtBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> can not delete sa.
> "Beige Bond" <BeigeBond@.hotmail.com> wrote in message
> news:OvFRYk0TFHA.2556@.TK2MSFTNGP12.phx.gbl...
>> Blocking the attacking IP probably won't help -- it's probably a dynamic
> IP.
>> This might be painful, but...create a new admin. login with a
> hard-to-guess
>> name, use a strong password, and DELETE sa.
>> -- Jeff
>> "A.M" <Hate-Spam@.nowhere.com> wrote in message
>> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>> > Hi,
>> >
>> >
>> >
>> > My SQL server is has a public IP address admins and developers can
> access
>> > and manage remotely (this is the requirement)
>> >
>> >
>> >
>> > When I monitor the SQL server port I can see some one is brutally
>> > trying
>> > to hack my server. There are almost 70 connections from IP address
>> > 66.15.173.105 that try to connect to my server.
>> >
>> >
>> >
>> > My guess is he is trying to use brute force technique to find the sa
>> > password.
>> >
>> >
>> >
>> > What is the best way to prevent that? Can I adjust some settings, to
> have
>> > failed logins wait for 10 seconds?
>> >
>> >
>> >
>> > Any help would be appreciated,
>> >
>> > Alan
>> >
>> >
>> >
>> >
>> >
>> >
>>
>|||In addition to all of these suggestions, you can also use the firewall
to block access from all outside IPs except those used by the admins
and developers.
Still the solution is to get a VPN setup and get the SQL Server box
off the internet. To expensive you say? How much will it cost your
company *when* the hacker gets through? I bet it's more than the cost
of some VPNs.
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Sat, 30 Apr 2005 11:39:51 -0400, "Michael C#" <xyz@.abcdef.com>
wrote:
>Change the port from 1433, block that IP address, make the sa password
>something ridiculously difficult (or better yet, switch to Windows
>integrated security only) and implement VPN.
>"A.M" <Hate-Spam@.nowhere.com> wrote in message
>news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> My SQL server is has a public IP address admins and developers can access
>> and manage remotely (this is the requirement)
>>
>> When I monitor the SQL server port I can see some one is brutally trying
>> to hack my server. There are almost 70 connections from IP address
>> 66.15.173.105 that try to connect to my server.
>>
>> My guess is he is trying to use brute force technique to find the sa
>> password.
>>
>> What is the best way to prevent that? Can I adjust some settings, to have
>> failed logins wait for 10 seconds?
>>
>> Any help would be appreciated,
>> Alan
>>
>>
>>
>|||Thank you everyone for your help and suggestions. I convinced development
team to use terminal services instead.
However, lack of proper password policy for SQL server is quite scary! It is
easy build a try/error program to hack sa password based on available
password dictionary databases!
Thanks again,
Alan
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My SQL server is has a public IP address admins and developers can access
> and manage remotely (this is the requirement)
>
> When I monitor the SQL server port I can see some one is brutally trying
> to hack my server. There are almost 70 connections from IP address
> 66.15.173.105 that try to connect to my server.
>
> My guess is he is trying to use brute force technique to find the sa
> password.
>
> What is the best way to prevent that? Can I adjust some settings, to have
> failed logins wait for 10 seconds?
>
> Any help would be appreciated,
> Alan
>
>
>|||That's probably why Windows Integrated is the recommended security model.
Why duplicate all of Windows' password policy functionality in SQL Server
when it's already accessible via Integrated Security?
Are you exposing Terminal Services to the Internet as well, or are you
making them use VPN? Don't forget the Administrator account on your domain
and local machines, which can usually use Terminal Services to log in as
well.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:%23YGjqxOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
>
> Thank you everyone for your help and suggestions. I convinced development
> team to use terminal services instead.
>
> However, lack of proper password policy for SQL server is quite scary! It
> is easy build a try/error program to hack sa password based on available
> password dictionary databases!
>
> Thanks again,
> Alan
>
> "A.M" <Hate-Spam@.nowhere.com> wrote in message
> news:e%235pS4YTFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> My SQL server is has a public IP address admins and developers can access
>> and manage remotely (this is the requirement)
>>
>> When I monitor the SQL server port I can see some one is brutally trying
>> to hack my server. There are almost 70 connections from IP address
>> 66.15.173.105 that try to connect to my server.
>>
>> My guess is he is trying to use brute force technique to find the sa
>> password.
>>
>> What is the best way to prevent that? Can I adjust some settings, to have
>> failed logins wait for 10 seconds?
>>
>> Any help would be appreciated,
>> Alan
>>
>>
>>
>

Please help: Shrink SQL Server database

See Books OnLine regarding dbcc shrinkfile and
shrinkdatabase.

>--Original Message--
>Hi,
>I created a database for test purposes. I am trying to
obtain some
>metrics for various scenarios I wish to test. Many of
these scenarios
>involve bulk inserts. Rather then deleting all rows after
each run I
>simply dropped and recreated the tables. However I've
noticed that the
>mdf and log files are getting larger and larger even when
the database
>is completely empty of data. How can I safely shrink the
size of these
>files?
>Thanks,
>Paul
>.
>Thanks

Please help: Shrink SQL Server database

Hi,
I created a database for test purposes. I am trying to obtain some
metrics for various scenarios I wish to test. Many of these scenarios
involve bulk inserts. Rather then deleting all rows after each run I
simply dropped and recreated the tables. However I've noticed that the
mdf and log files are getting larger and larger even when the database
is completely empty of data. How can I safely shrink the size of these
files?
Thanks,
PaulSee Books OnLine regarding dbcc shrinkfile and
shrinkdatabase.
>--Original Message--
>Hi,
>I created a database for test purposes. I am trying to
obtain some
>metrics for various scenarios I wish to test. Many of
these scenarios
>involve bulk inserts. Rather then deleting all rows after
each run I
>simply dropped and recreated the tables. However I've
noticed that the
>mdf and log files are getting larger and larger even when
the database
>is completely empty of data. How can I safely shrink the
size of these
>files?
>Thanks,
>Paul
>.
>|||You could shrink the database, but beware that if you're going to occupy
that space again, you're better off leaving the file large. Otherwise, you
will go through the performance problem of autogrowing during a transaction
to make room again.
http://www.aspfaq.com/2471
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Paul" <paulsmith5@.hotmail.com> wrote in message
news:ca236fb1.0402170925.125f087e@.posting.google.com...
> Hi,
> I created a database for test purposes. I am trying to obtain some
> metrics for various scenarios I wish to test. Many of these scenarios
> involve bulk inserts. Rather then deleting all rows after each run I
> simply dropped and recreated the tables. However I've noticed that the
> mdf and log files are getting larger and larger even when the database
> is completely empty of data. How can I safely shrink the size of these
> files?
> Thanks,
> Paul|||First off you should use SIMPLE recovery mode and then use DBCC SHRINKFILE
to shrink the files.
--
Andrew J. Kelly
SQL Server MVP
"Paul" <paulsmith5@.hotmail.com> wrote in message
news:ca236fb1.0402170925.125f087e@.posting.google.com...
> Hi,
> I created a database for test purposes. I am trying to obtain some
> metrics for various scenarios I wish to test. Many of these scenarios
> involve bulk inserts. Rather then deleting all rows after each run I
> simply dropped and recreated the tables. However I've noticed that the
> mdf and log files are getting larger and larger even when the database
> is completely empty of data. How can I safely shrink the size of these
> files?
> Thanks,
> Paul|||Thanks|||Thanks Andrew|||Thanks Aaron,
The performance problem of autogrowing is another thing I should look at.|||In Enterprise Manager
1. Right click on the database in question
2. All Tasks -> Shrink Databases
3. Put in a Maximum Free space of say 10%
4. Check the "Move Pages to beginning....."
5. Press OK
...and that should do it.
J
>--Original Message--
>Hi,
>I created a database for test purposes. I am trying to
obtain some
>metrics for various scenarios I wish to test. Many of
these scenarios
>involve bulk inserts. Rather then deleting all rows after
each run I
>simply dropped and recreated the tables. However I've
noticed that the
>mdf and log files are getting larger and larger even when
the database
>is completely empty of data. How can I safely shrink the
size of these
>files?
>Thanks,
>Paul
>.
>

please help: 'PRIMARY' filegroup is full

Hi, all,
I got the error message 'Could not allocate space for
object 'B_Table' in database 'UDB' because the 'PRIMARY'
filegroup is full..' and I check the error log file there
is error message like this 'C:\MSSQL7\data\UDB_Data.MDF:
Operating system error 112(There is not enough space on
the disk.) But the truth is there still is 44G free space.
Both the data and log files are setting auto grow 10%.
why I get this kind of error? how can i fix it?
Thanks!
HawkHello Hawk!
1.Could it be that there are quotas on the disk ? Therefore, remove qutoas
for the SystemAccount you are starting SQL Server with. (What does the event
log say ?)
(2.If not, try to resize the data file via QA and scrpt code using ALTER
Database.
3.If This doesn´t do the right thing, add another data file to the primary
file group and try to get around. (but remeber, this is only a woraround,
not the best idea, it is only to test the case that the file could not be
resized))
Jens Süßmeyer.
"hawk" <abchawk@.hotmail.com> schrieb im Newsbeitrag
news:01c401c36671$a339fd10$a301280a@.phx.gbl...
> Hi, all,
> I got the error message 'Could not allocate space for
> object 'B_Table' in database 'UDB' because the 'PRIMARY'
> filegroup is full..' and I check the error log file there
> is error message like this 'C:\MSSQL7\data\UDB_Data.MDF:
> Operating system error 112(There is not enough space on
> the disk.) But the truth is there still is 44G free space.
> Both the data and log files are setting auto grow 10%.
> why I get this kind of error? how can i fix it?
> Thanks!
> Hawk|||To make it bigger, use
ALTER DATABASE xxx
MODIFY FILE...
Info in Books Online.
Or use EM, Properties for the DB and specify a bigger size for the file.
To shrink, yes, use DBCC SHRINKFILE. I do not recommend regular shrinking. That just mean that the
db jumps up and down in size which is a costly operation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hawk" <abchawk@.hotmail.com> wrote in message news:0e9501c36722$2c3362e0$a301280a@.phx.gbl...
> Hi, Dan, Thank you very much.
> If my Data File size is 4.2GB right now and I get the
> primary filegroup is full. then I should resize the data
> file bigger, for example resize to 6.0GB or 7.0GB,right?
> Could I use DBCC DHRINKFILE to shrink the data file?
> I don't know what should I do to down size the data file
> or resize it bigger.
> Thank you very much.
> Have a good one
>
>

Please help: object reference not set to an instance of an object

I have created a new local website from the "Personal Web Site Starter Kit" template. I got an error message when i double click on the database file:

"Object reference not set to an instance of an object."

I cannot open Personal.mdf in VS2005.

I have not tuch anything. SQL Server 2005 Express is running in bacground. What is wrong?

This sounds like something is not registered correctly in Visual Studio.

The ASP.net forums is a better place to ask questions about ASP.net and Visual Web Developer. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

Mike

Please help: insert into table in cursor-loop?

Hi, all,

I'd like to insert returned items into the result table @.r:

Create function get_items

returns @.r table(a1 varchar(30),a2 varchar(30),a3 varchar(30),a4 varchar(30),a5 varchar(30))

as
begin

declare @.v_item nvarchar (30);

declare @.v_count int;

declare cur_items cursor for

select top 5 a.item from itemtable a; -- gets max. 5 items!

open cur_items;

fetch next from cur_items into @.v_item;

set @.v_count = 0;

while (@.@.fetch_status = 0)

begin

set @.v_count = @.v_count+1;

-- Problem:

-- Insert into @.r(a1,a2...) values(@.v_item)... ?

fetch next from cur_items into @.v_item;

end;

close cur_items;

DEALLOCATE cur_items;

return

END

=========

That means,

if @.v_count = 1,

@.r has only one item, such as: 'item1', <null>, <null>, <null>, <null>

but if @.v_count = 5,

@.r has full-row, such as: 'item1', 'item2', 'item3', 'item4', 'item5'

Thank you very much in advance!

If I understand your problem correctly, you don't need a cursor. Use a table valued function (TVF).

Something like this:

CREATE FUNCTION Get_Items ()
RETURNS table
AS
RETURN
( SELECT TOP 5 Item
FROM ItemTable
)

|||

Hello, Arnie Rowland, thanks for your answer!

In my code I have to use the cursor, the definition of the cursor above is just example,

and the result from the cursor is: 'item1', 'item2'... or more, but max. 5 items.

Best regards

|||

Hi, all,

maybe I have to convert all rows of the result table to one row?

after execute the function I got e.g. 3 rows:

item1

item2

item3

==>

if I can convert them to one row, then I have:

item1 | item2 | item3 | <null> | <null>

How can I get it?

Best regards

|||

Here are some resources that may help you get your desired output.

I highly recommend NOT using a cursor if at all possible -and in almost all data retrieval situations, it is possible!

Lists -Field Concatenation
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

Lists -Field Concatenation( For SQL 2000 & 2005 )
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

Lists -Recursive Queries
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=9
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03i8.asp
http://www.wwwcoder.com/main/parentid/191/site/1857/68/default.aspx
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp

Please help: how to convert database to unicode?

I already posted this question some time ago but I didn't get any answer, so please forgive for posting it again.

We plan to support Unicode in our next software version. This means that we will need to upgrade many (> 200) databases from our customers from single byte collation to Unicode. Is there an easy way to do this with SQL Server?

With Oracle we can dump the current database, create a new instance that supports Unicode and then import the dump and all char fields get automatically converted to unicode.

As I see it, it doesn't seem to be so easy with SQL Server. At the moment my solution would be to create a new database schema with all the columns changed to the N types (nvarchar, ntext, ...) and then use a DTS Package to transfer the "old" data to the new database. This seems like a lot of work, so that I would like to know if someone has a better idea.

Thank you in advance for your ideas!!

Raul

Im not sure but try changing the collation type. It should change the underlying data to the new collation type.. jst a thought.

with smiles

santhosh

|||Raul

If you are merely changing columns from CHAR to NCHAR and VARCHAR to NVARCHAR, you should be able to just perform ALTER TABLE ALTER COLUMN statements to change the columns to unicode.

CREATE TABLE dbo.UTest
( C CHAR(10) NOT NULL,
VC VARCHAR(10) NOT NULL
)
GO
INSERT INTO dbo.UTest(C, VC) VALUES ('Test', 'Test')
GO
ALTER TABLE dbo.UTest
ALTER COLUMN C NCHAR(10) NOT NULL
GO
ALTER TABLE dbo.UTest
ALTER COLUMN VC NVARCHAR(10) NOT NULL
GO
SELECT * FROM dbo.UTest

Please help: Formula Field Rename

hello,
Anyone knows how to programmtically rename a formula field of crytal reports in vb and foxpro?Why do you want to rename?

PLEASE HELP: Force SQL Login only using SQL SERVER Autherntication.

Hi SQL Gurus,
I am still not a master in SQL Server.
How can we restrict user from login using "WINDOWS AUTHENTICATION".
We don't want anyone who has the server windows login to access SQL Server.
We have seen some users logged in to the database server, when they should
not have access.
Please help me how to restrict them accessing the server.
Thanks for any help or suggestion you can provide.
Mark.Hi
If the users have not directly been granted a login, then they probably have
been granted logins through a windows group. Make sure that they are not
members of a group they should not be in or a group with a wide membership
has been granted a login. You can use sp_denylogin to deny access to a given
login.
John
"Mark" <Mark@.nospaml.com> wrote in message
news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi SQL Gurus,
> I am still not a master in SQL Server.
> How can we restrict user from login using "WINDOWS AUTHENTICATION".
> We don't want anyone who has the server windows login to access SQL
> Server.
> We have seen some users logged in to the database server, when they should
> not have access.
> Please help me how to restrict them accessing the server.
> Thanks for any help or suggestion you can provide.
> Mark.
>|||> How can we restrict user from login using "WINDOWS AUTHENTICATION".
Well, you didn't bother to mention which version of SQL Server you are
using... if you are using SQL Server 2005, you could put the sa password in
a very, very, very safe place, and then create a LOGON TRIGGER, e.g.
USE master;
GO
CREATE TRIGGER BlockLogin
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() LIKE '%\%'
ROLLBACK
END
GO
> We have seen some users logged in to the database server, when they should
> not have access.
Do you mean logged onto the physical server (e.g. through terminal
services), or SQL Server itself? Where have you seen them?
The former, I assume, would be controlled through Active Directory etc., and
not by SQL Server.
A|||We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
fine
Do you think there are any isses if I delete this login?
Thanks, Mark
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Hi
> If the users have not directly been granted a login, then they probably
> have been granted logins through a windows group. Make sure that they are
> not members of a group they should not be in or a group with a wide
> membership has been granted a login. You can use sp_denylogin to deny
> access to a given login.
> John
> "Mark" <Mark@.nospaml.com> wrote in message
> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
>|||Mark,
The only issue is to make sure that someone still has administrator rights
so that it is still possible to administer the server. Getting
BUILTIN\ADMINISTRATOR out of your SQL Server is an excellent practice.
http://www.sqlservercentral.com/articles/Security/10securingyoursqlserver/701/
RLF
"Mark" <Mark@.nospaml.com> wrote in message
news:OerAowbmIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
> fine
> Do you think there are any isses if I delete this login?
> Thanks, Mark
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> If the users have not directly been granted a login, then they probably
>> have been granted logins through a windows group. Make sure that they are
>> not members of a group they should not be in or a group with a wide
>> membership has been granted a login. You can use sp_denylogin to deny
>> access to a given login.
>> John
>> "Mark" <Mark@.nospaml.com> wrote in message
>> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
>>
>|||"Mark" <Mark@.nospaml.com> wrote in message
news:OerAowbmIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
> fine
> Do you think there are any isses if I delete this login?
> Thanks, Mark
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> If the users have not directly been granted a login, then they probably
>> have been granted logins through a windows group. Make sure that they are
>> not members of a group they should not be in or a group with a wide
>> membership has been granted a login. You can use sp_denylogin to deny
>> access to a given login.
>> John
>> "Mark" <Mark@.nospaml.com> wrote in message
>> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
Hi Mark
I think the issue you should address is why these users are over privileged
as Russell suggests. Removing anyone who does not need the local
administrators privilege should be an urgent priority, in the worst case
they will also be domain administrators.
John|||Thanks Guys.
I got the answer I was looking for.
Thanks again, Mike
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uQR%23lUimIHA.944@.TK2MSFTNGP05.phx.gbl...
> "Mark" <Mark@.nospaml.com> wrote in message
> news:OerAowbmIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
>> fine
>> Do you think there are any isses if I delete this login?
>> Thanks, Mark
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> If the users have not directly been granted a login, then they probably
>> have been granted logins through a windows group. Make sure that they
>> are not members of a group they should not be in or a group with a wide
>> membership has been granted a login. You can use sp_denylogin to deny
>> access to a given login.
>> John
>> "Mark" <Mark@.nospaml.com> wrote in message
>> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
> Hi Mark
> I think the issue you should address is why these users are over
> privileged as Russell suggests. Removing anyone who does not need the
> local administrators privilege should be an urgent priority, in the worst
> case they will also be domain administrators.
> John
>

Please Help: Error: Missing semicolon (;) at end of SQL statement.

Hey

I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.

I am recieving this error and don't know why, i have the following coed below.


Dim objReaderQ as OleDBDataReader Dim strSQLRead As String
 Dim objCmd As New OleDbCommand
 strSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"
 objCmd = new OleDbCommand(strSQLRead, objConn)
 objReaderQ = objCmd.ExecuteReader()
 if objReaderQ.Read()
 'update quantity by 1
 Dim i as integer
 i = objReaderQ("quantity")
 i = i + 1
 objReaderQ.Close()
 Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@.quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"
 Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)
 objCmdQuantity.Connection = objConn
 objCmdQuantity.Parameters.Add("@.quantity", OleDbType.VarChar, 255)
 objCmdQuantity.Parameters("@.quantity").Value = i
 objCmdQuantity.ExecuteNonQuery() ' <-- Error Is Occuring On This Line
 Response.Redirect("ViewBasket.aspx")
 end if

I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.

Thanks you for your time

ChrisHey,

I have solved this problem, wrong sql statement, should be update! lol :(

But i do have the problem that once go to the viewbasket.aspx page it shows the product with the quantity 1, as it pulls the quantity from the db and its default value is 1 (which is correct).

But now if that same product is clicked 'Add To basket' for a second time, it executes the above code and goes to the viewbasket.aspx page, but the quantity stays as 1 ! (should be 2)

And now if that same product is clicked 'Add To basket' for a third time, it executes the above code again and goes to the viewbasket.aspx page, but this time the quantity is 2 ! (should be 3)

From this point on the code worked fine and increments the number properly, 4,5,6 etc..

Any idea why the first two clicks dont work ?

Thank you for your help

Chris

Please help: date-only comparison

We have a select query on SQL server 2K with some date criteria like this:
transaction_date + 30 <= end_date
Because the data type of the date fields is "Datetime", the comparison gets
to time. This may cause missing records when (transaction_date + 30 =
end_date).
So I tried with Date-only as below:
convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
end_date)
or
convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
end_date, 120)
But this does not work! Please advise on how to truncate TIME in this case.
Thanks!
JohnGet calculations out of one side.
WHERE transaction_date < (convert(smalldatetime, convert(char(8), end_date,
112)) - 29)
If you give some table structure, sample data, and desired results
(including rows that make the solution to "does not work", whatever that
means), we can give a more authoritative answer. Please read
http://www.aspfaq.com/5006 to see why it is important to include more
specific, well, specs.
"John61" <wanghaodong@.yahoo.com> wrote in message
news:uQ4dNkscGHA.4224@.TK2MSFTNGP04.phx.gbl...
> We have a select query on SQL server 2K with some date criteria like this:
> transaction_date + 30 <= end_date
> Because the data type of the date fields is "Datetime", the comparison
> gets to time. This may cause missing records when (transaction_date + 30 =
> end_date).
> So I tried with Date-only as below:
> convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
> end_date)
> or
> convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
> end_date, 120)
> But this does not work! Please advise on how to truncate TIME in this
> case.
> Thanks!
> John
>
>|||Well I don't see a problem.
If you exactly knowthe row which is is is missing or erronously displayed,
then you can do a check on it and see why its getting displayed.
Like
select convert(varchar(10), transaction_date + 30, 120) ,convert(varchar(10)
,
end_date, 120), <other columns>
from all tables
where
conditions
--comment the following condition and check the result set. Maybe that will
give you an insight.
--and convert(varchar(10), transaction_date + 30, 120) <=
convert(varchar(10),
end_date, 120)
Hope this helps.
"John61" wrote:

> We have a select query on SQL server 2K with some date criteria like this:
> transaction_date + 30 <= end_date
> Because the data type of the date fields is "Datetime", the comparison get
s
> to time. This may cause missing records when (transaction_date + 30 =
> end_date).
> So I tried with Date-only as below:
> convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
> end_date)
> or
> convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
> end_date, 120)
> But this does not work! Please advise on how to truncate TIME in this case
.
> Thanks!
> John
>
>

Please help: Create View

Hi Gurus,
I'm a beginner. Would you please tell me if it's possible to create a view
having a calcuated column based on the condition of the column on the sql
table.

create view vwImaging AS
select
EmpID, LastName, FirstName, EmpTag = 'Act' if

FROM tblPerPay
I have a table EMP:
SSN (char 9)

A view giving a formatted SSN (XXX-XX-XXXX,) a

--
Message posted via http://www.sqlmonster.comSorry, I press the "Post Message" by accident while editing the message.

Here is question again: Is it possible to create a view having a column
based on the value of a given column on the sql table.

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTag = 'Act' (if tblEmp.TermDate is Null)
EmpTag = 'Inact' (if tblEmp.TermDate not Null)
from tblEmp

I don't know the syntax of the last 2 columns. TermDate is the termination
date in tblEmp.

Any help will be greatly appreciated.
Thanks in advance
TTran

--
Message posted via http://www.sqlmonster.com|||"T Tran via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c015ee60567f4767934b62908ec07144@.SQLMonster.c om...
> Sorry, I press the "Post Message" by accident while editing the message.
> Here is question again: Is it possible to create a view having a column
> based on the value of a given column on the sql table.
> create view LookUp AS
> select
> EmpID,
> LastName,
> FirstName,
> EmpTag = 'Act' (if tblEmp.TermDate is Null)
> EmpTag = 'Inact' (if tblEmp.TermDate not Null)
> from tblEmp
> I don't know the syntax of the last 2 columns. TermDate is the termination
> date in tblEmp.
> Any help will be greatly appreciated.
> Thanks in advance
> TTran
> --
> Message posted via http://www.sqlmonster.com

Check out CASE in Books Online. Do you need two EmpTag columns? If Act/Inact
is a flag, it may make more sense to use only one column:

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTag = case when TermDate is Null then 'Act' else 'Inact' end
from
tblEmp

But if you do need separate columns, then try this (it's usually not a good
idea to return multiple columns with the same name):

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTagAct = case when TermDate is null then 'Act' else '-' end,
EmpTagInact = case when TermDate is not null then 'Inact' else '-' end
from
tblEmp

Simon|||Since you are learning SQL, you might want to start by learning
ISO-11179 rules for names and the Standard SQL syntax for aliases. A
CASE expression will handle this:

CREATE VIEW PersonnelStatus (ssn, last_name, first_name,
employment_status)
AS
SELECT ssn, last_name, first_name,
CASE (WHEN term_date IS NULL
THEN 'inactive'
ELSE 'active ' END
FROM Personnel;

The equal sign is local dialect; the AS operator is Standard. Use
collective or plural nouns for table names; never put a prefix on a
data element to tell us how it is *physically* stored. This is really
silly in SQL, since there is only one data structure.

I have a book on SQL PROGRAMMING STYLE due out the middle of this year
that might be of some help to you.

Please Help: Cannot Sort row of size 8104: any advice.

I have a database linked too a website that, we've just put the live data into and hay presto the thing has fallen over. The data has gone in fine but When we search on it it coming up with this error:

Microsoft OLE DB Provider for SQL Server (0x80040E14) Cannot sort a row of size 8104, which is greater than the allowable maximum of 8094.

Has anyone come across this kinda of problem before and is their any nice work arounds. Thanks EdObviously, you managed to get a row size which is 10 more than the maximum. Consider to shorten one of your VARCHAR(x) fields by 10.|||Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.

So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?

If it any help the SQL look like this:

SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName

And the field causing the problems is:
terms.TermShortDesc

Thanks Again|||Originally posted by Nixies
Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.

So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?

If it any help the SQL look like this:

SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName

And the field causing the problems is:
terms.TermShortDesc

Thanks Again

I'm assuming that TermShortDesc is a text field not a varchar.

You have two possible solutions:

Fix the problem short term:
SELECT terms.AutoId, LEN(terms.TermShortDesc) AS CHAR_LEN, terms.TermShortDesc
FROM TERMS
WHERE LEN(terms.TermShortDesc) >8093.
and then edit that data down in length.

The permanent solution is:
Before you get to deep into this, ask yourself these questions:
Do you have to search the entire field? Can you limit data to 8093 characters? In your search do you really have to go beyond the first 500 characters? 1000?

For a permanent solution if you only need the first 500 characters, then make an in your import that TermShortDescSort field and just take the LEFT(TermShortDesc,500) and insert them into TermShortDescSort on import. If you need to go beyond and have all 24,282, then make 3 fields TermShortDesc1, TermShortDesc2, TermShortDesc3 and on import insert into them as Substr(TermShortDesc,1,8094), Substr(TermShortDesc,8095,8094), Substr(TermShortDesc,16188,8094).

Just throwing in my 2 centavos....|||This is fantasic, we've found the offending rows and everything is up and working, Thanks for your Help, Ed

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the first
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Service
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.
did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message
|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =
1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:

> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>

Saturday, February 25, 2012

Please Help: SQL service cannot start

Please help: SQL Server Saving performance problem.

Please help: SQL Server Saving performance problem.

Please help: SQL Server Saving performance problem.

Please Help: Someone is hacking my server!

Please Help: Someone is hacking my server!

Please Help: Someone is hacking my server!

Please Help: Someone is hacking my server!

Please help: Shrink SQL Server database

Please help: Shrink SQL Server database

Please help: Shrink SQL Server database

please help: 'PRIMARY' filegroup is full

Please help: object reference not set to an instance of an object

Please help: insert into table in cursor-loop?

Please help: how to convert database to unicode?

Please help: Formula Field Rename

PLEASE HELP: Force SQL Login only using SQL SERVER Autherntication.

PLEASE HELP: Force SQL Login only using SQL SERVER Autherntication.

Please Help: Error: Missing semicolon (;) at end of SQL statement.

Please help: date-only comparison

Please help: Create View

Please Help: Cannot Sort row of size 8104: any advice.

Please help: Can not start Report Server Web Service for 2000

SQL compact edition

Blog Archive

About Me