Norton Internet Security & SQL Server

Hi,

Just installed Norton Internet Security on an XP workstation that also has
SQL Server on it.

I now find that I cannot access SQL Server and multiple messages are being
issued by NIS.

When I switch off the Firewall & Intrusion Detection I can access SQL
Server.

Does anyone know how to configure NIS so that I can use it alongside SQL
Server?

Thanks,

Mike.Uytkownik "Mike Stogden" <news@.uniquest.demon.co.uk> napisa w wiadomoci
news:bhaolh$i5i$1$8300dec7@.news.demon.co.uk...
> Hi,
> Just installed Norton Internet Security on an XP workstation that also has
> SQL Server on it.
> I now find that I cannot access SQL Server and multiple messages are being
> issued by NIS.
> When I switch off the Firewall & Intrusion Detection I can access SQL
> Server.
> Does anyone know how to configure NIS so that I can use it alongside SQL
> Server?
Hi, try this:

permit UDP OUT and IN protocol for

SQL Server Service Manager (sqlmangr.exe)
SQL Server Windows NT (sqlserver.exe)

And TCP OUT and IN protocol for

SQL Tracing Tool (profiler.exe)
ISQL (isqlw.exe)

from any adress:any port and this should work, and then try fit your rules
to be more security

best regards
Marcin D

Northwind and pubs default login/password ?

Hello,

What are the default login/password to access pubs and northwind databases ?
I remember the login = "sa", but nothing about the password.

Thanks

EricEricP (eric.piquot@.wanadoo.fr) writes:
> What are the default login/password to access pubs and northwind
> databases ? I remember the login = "sa", but nothing about the password.

Password are associated with logins and not with databases. And logins
are associated with servers and not with databases.

The default password for "sa" used to be blank, but hopefully this has
been changed on your server.

You can also log with in with Windows authentication. In this case you
don't provide a password, since you are already logged into Windows.
(Unless you are using Win 98 or somesuch.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sorry for my badly English,

Erland Sommarskog a crit :
> EricP (eric.piquot@.wanadoo.fr) writes:
>>What are the default login/password to access pubs and northwind
>>databases ? I remember the login = "sa", but nothing about the password.
>
> Password are associated with logins and not with databases. And logins
> are associated with servers and not with databases.
I don't understand:
3 databases A, B, C on a server.
Hope one distinct (login,password) per database for the same user.
> The default password for "sa" used to be blank, but hopefully this has
> been changed on your server.
> You can also log with in with Windows authentication. In this case you
> don't provide a password, since you are already logged into Windows.
> (Unless you are using Win 98 or somesuch.)
Right

Thanks|||EricP (eric.piquot@.wanadoo.fr) writes:
> I don't understand:
> 3 databases A, B, C on a server.
> Hope one distinct (login,password) per database for the same user.

Not sure that I understand where you heading at.

But here is how security works in SQL Server. On server level you have
logins. A login can be an SQL login or a Windows login. SQL logins have
passwords, Winodws login have not (in SQL Server; they have in Windows
of course). A login can be associated with a user in a database. Most
often login name and user name are the same, but not there is no law
that requires this. Thus, once a person have logged into SQL Server,
he can access several databases, but if he is not added as user in
a database, he has no permission to it. (I'm bere overlooking the fact
that he may own a database ae well.)

If I understand you correctly, you want the same physical person have
three different (login, password) for three different databases. In
such case this person needs to have three different logins in SQL Server,
and this means that you need to use SQL logins, or else the person will
have to change between different Windows users.

There is one more alternative, and that is application roles. With an
application role you can get access to objects in a data to which
permissions have been granted to that role. To get access to an application
role, you need to supply the password. The typical use of an
application role is that you issue sp_setapprole from the application,
and the password is embedded in the application somewhere; thus the
users never supply it, or even know about it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog a crit :
> If I understand you correctly, you want the same physical person have
> three different (login, password) for three different databases. In
> such case this person needs to have three different logins in SQL Server,
> and this means that you need to use SQL logins, or else the person will
> have to change between different Windows users.

That's quiet.
Thankssql

Normalizing help and naming conventions

I have an Access Database that I am working on normalizing to SQL Server.
This Access database has been revised multiple times, each time the
programmer (not me!) didn't work to get the existing data to the same level
as the revised program. For instance, the HairColor column has the
following DISTINCT values,
Auburn
Black
Black/Gray
Blonde
Brown
Brown/Gray
Dark Brown
Gray
Gray/Black
Gray/Brown
Lt. Brown
Red
Red-blonde
Red/Blonde
Red/Brown
Now the current revision of the Access database limits the values for the
HairColor column to,
Black
Blonde
Brown
Gray
Red
So I need to make the current values reflect the current dropdown. I know
how to go about this, but have a question about normalizing this database.
Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of just
leaving the text in there?
Also, if I am to create a new table, how does the naming convention work
with that? Should I prefix my table name with fk_HairColor, or something
like that?
Any advice is appreciated!
Thanks!
drewI would create a table named HairColor with the columns:
HairColorID int
HairColor varchar (20)
In any table that references this table, you can create a foreign key. You
will take up less space in your DB overall. Also, consider what would
happen if you decided to change "Lt. Brown" to "Light Brown". With the
normalized version, you change it in only one place.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
I have an Access Database that I am working on normalizing to SQL Server.
This Access database has been revised multiple times, each time the
programmer (not me!) didn't work to get the existing data to the same level
as the revised program. For instance, the HairColor column has the
following DISTINCT values,
Auburn
Black
Black/Gray
Blonde
Brown
Brown/Gray
Dark Brown
Gray
Gray/Black
Gray/Brown
Lt. Brown
Red
Red-blonde
Red/Blonde
Red/Brown
Now the current revision of the Access database limits the values for the
HairColor column to,
Black
Blonde
Brown
Gray
Red
So I need to make the current values reflect the current dropdown. I know
how to go about this, but have a question about normalizing this database.
Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of just
leaving the text in there?
Also, if I am to create a new table, how does the naming convention work
with that? Should I prefix my table name with fk_HairColor, or something
like that?
Any advice is appreciated!
Thanks!
drew|||Thanks for the response... I thought it was better to replace it with a FK,
but am trying to convince my boss the same...
So you don't use a different naming convention with FKs? I just have about
30 tables on this database and it is getting harder to read for each FK
table I include...
Thanks,
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>I would create a table named HairColor with the columns:
> HairColorID int
> HairColor varchar (20)
> In any table that references this table, you can create a foreign key.
> You
> will take up less space in your DB overall. Also, consider what would
> happen if you decided to change "Lt. Brown" to "Light Brown". With the
> normalized version, you change it in only one place.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
> I have an Access Database that I am working on normalizing to SQL Server.
> This Access database has been revised multiple times, each time the
> programmer (not me!) didn't work to get the existing data to the same
> level
> as the revised program. For instance, the HairColor column has the
> following DISTINCT values,
> Auburn
> Black
> Black/Gray
> Blonde
> Brown
> Brown/Gray
> Dark Brown
> Gray
> Gray/Black
> Gray/Brown
> Lt. Brown
> Red
> Red-blonde
> Red/Blonde
> Red/Brown
> Now the current revision of the Access database limits the values for the
> HairColor column to,
> Black
> Blonde
> Brown
> Gray
> Red
> So I need to make the current values reflect the current dropdown. I know
> how to go about this, but have a question about normalizing this database.
> Should I create a seperate table, HairColor and then assign ID's to the
> HairColor column? What is the benefit of doing it this way, instead of
> just
> leaving the text in there?
> Also, if I am to create a new table, how does the naming convention work
> with that? Should I prefix my table name with fk_HairColor, or something
> like that?
> Any advice is appreciated!
> Thanks!
> drew
>|||I did say to use a foreign key. Here's an example. Let's say that you have
a Persons table, and each person has a hair colour. Here's how the Persons
table would go:
create table Persons
(
PersonID int primary key
, HairColorID int not null
FOREIGN KEY FK1_Persons
REFERENCES HairColor
, ...
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:uW2Bs7LXGHA.196@.TK2MSFTNGP04.phx.gbl...
Thanks for the response... I thought it was better to replace it with a FK,
but am trying to convince my boss the same...
So you don't use a different naming convention with FKs? I just have about
30 tables on this database and it is getting harder to read for each FK
table I include...
Thanks,
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>I would create a table named HairColor with the columns:
> HairColorID int
> HairColor varchar (20)
> In any table that references this table, you can create a foreign key.
> You
> will take up less space in your DB overall. Also, consider what would
> happen if you decided to change "Lt. Brown" to "Light Brown". With the
> normalized version, you change it in only one place.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
> I have an Access Database that I am working on normalizing to SQL Server.
> This Access database has been revised multiple times, each time the
> programmer (not me!) didn't work to get the existing data to the same
> level
> as the revised program. For instance, the HairColor column has the
> following DISTINCT values,
> Auburn
> Black
> Black/Gray
> Blonde
> Brown
> Brown/Gray
> Dark Brown
> Gray
> Gray/Black
> Gray/Brown
> Lt. Brown
> Red
> Red-blonde
> Red/Blonde
> Red/Brown
> Now the current revision of the Access database limits the values for the
> HairColor column to,
> Black
> Blonde
> Brown
> Gray
> Red
> So I need to make the current values reflect the current dropdown. I know
> how to go about this, but have a question about normalizing this database.
> Should I create a seperate table, HairColor and then assign ID's to the
> HairColor column? What is the benefit of doing it this way, instead of
> just
> leaving the text in there?
> Also, if I am to create a new table, how does the naming convention work
> with that? Should I prefix my table name with fk_HairColor, or something
> like that?
> Any advice is appreciated!
> Thanks!
> drew
>|||>> Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of
just
leaving the text in there? <<
Let's start with basics:
1) Do your research! Is there an industry standard code for hair
colors? Use it
2) If not, then design an encoding that has a missing and/or misc code
value -- Hey, I am bald!
3) If you expect the values to change, the list to be long, or that you
willneed to add other information later, then put the encoding into a
separate table.
4) If you expect the values to be static, the list to be short or that
you will not need to add other information later, then put the encoding
into a CHECK() constraint. For example, the ISO sex codes are going to
stay the same for awhile, as are the two-letter state codes.
Get a copy of SQL PROGRAMMING STYLE. You never, never prefix a data
element name with thigns to tell you (1) Where it is used -- so no
table names (2) how it is used -- so no pk- or fk- crap (3) how it is
store, so no data types. Follow ISO-11179 rules and name a thing for
what it is, independent of its location or usage.
Do not blindly design an encoding as a numbered list. Think about
hierarchical ("Dewey Decimal") codes, abbreviation codes, etc. That
is also in SQL PROGRAMMING STYLE.|||I second getting SQL Programming Style. I read it in 2 ws. It is
any easy read.|||For the record, I was agreeing with you!
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYsy3EMXGHA.752@.TK2MSFTNGP02.phx.gbl...
>I did say to use a foreign key. Here's an example. Let's say that you
>have
> a Persons table, and each person has a hair colour. Here's how the
> Persons
> table would go:
> create table Persons
> (
> PersonID int primary key
> , HairColorID int not null
> FOREIGN KEY FK1_Persons
> REFERENCES HairColor
> , ...
> )
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:uW2Bs7LXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response... I thought it was better to replace it with a
> FK,
> but am trying to convince my boss the same...
> So you don't use a different naming convention with FKs? I just have
> about
> 30 tables on this database and it is getting harder to read for each FK
> table I include...
> Thanks,
> Drew
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>

Normalize multivalue field?

Hi,
I have a table I'm importing from access; the application that used
the table has some fields that are multivalued, separated by either
a ; or the sequence /* Ugly.
I can write a C# app to take the columns and stuff the data into my
normalized schema, but I was wondering if there was any good way to do
it in sql.
Thanks,
AndyOn Sep 7, 12:57 pm, Andy <an...@.med-associates.com> wrote:
> Hi,
> I have a table I'm importing from access; the application that used
> the table has some fields that are multivalued, separated by either
> a ; or the sequence /* Ugly.
> I can write a C# app to take the columns and stuff the data into my
> normalized schema, but I was wondering if there was any good way to do
> it in sql.
> Thanks,
> Andy
Look up Erland Sommarskog's article "arrays and Lists in SQL"|||The best approach is to clean up the data to represent one value per column.
If, by any chance, you are forced to deal with such designs, you'll have to
resort to one of the string parsing approaches. Some of them can be found
at:
www.projectdmx.com/tsql/sqlarrays.aspx
www.projectdmx.com/tsql/rowconcatenate.aspx
--
Anith

Normalisation vs If it works just do it!

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)I'm a bit confused... When I use the term "place" to describe a horse racing bet, I mean that I expect (and am wagering that) the horse will finish second. While it is unusual to do, you can "box" a single horse, meaning that you expect them to place in the top 3, but you retrieve your original wager plus twenty percent of what the return would have been for a correct bet (win, place, or show), which sounds something like your description of a "place" bet.

If you can describe what you really want, I'm sure that someone here on the forum can show you how to code it!

-PatP|||Hi,

Thanks for the response, the actual calculations is not the issue e.g. the odds for a place bet, that is already known.

The problem concerns how to read through the table of placed bets while calculating the profit/loss as we iterate through each record.

The arithmetic is not the issue here, its the method of retrieval so that each record will have a field containing a profit or loss amount, put there by SQL as it reads through each item.

Thanks.|||Performance is the key in my work. If running the cursor is going to slow down the operation then having a little redundency isn't bad.|||My thought would be to do an UPDATE using the CASE to control which computation you use (main or place). Clean, fast, no cursor... What more could you ask?

-PatP

Noob Query Problem -This will be easy for you

This is driving me crazy. I am using a simple ASP table editor to make changes to an Access table. The following command is causing me to get an error:

UPDATE rates Set IRItype='A2', IRIProductName='Stylish', SQfeet='803', Bathrooms=1, IRIunitprice='600',

This is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The SQL command is automaticly generated by the ASP script. I do not know if that extra comma is the cause of the error.is that all there was to the UPDATE statement? if so, it produces an error because it ends with a dangling comma

typically, you would have a WHERE clause, unless your intention is to update all rows to those values

so yeah, look into the ASP script|||The extra comma is certainly a problem. Also, make sure SQfeet and IRIunitprice are text values in the table. If they are numeric, then you wouldn't want the single quote surrounding the values.

non-sa users get access denied in OLE DB query

On SQL Server 2005, 64 bit, this query:
SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
MISDB2A.FASMSCAC_STD_CARRIER_CODE')
Gets this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
I works if it is run by the sa login, or by a login that has been granted
the sysadmin Server Role.
How can I permission non-sa logins to access the OLE DB provider?Ad-hoc remote queries are disabled by default in SQL 2005. Check the
"Surface Area Configuration for Features (Ad Hoc Remote Queries) - Database
Engine " topic in BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/fe754945-2cd2-4cc1-8ae1
-4de01907b382.htm).
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:0896B540-3262-42A8-8D4E-97D900C35A91@.microsoft.com...
> On SQL Server 2005, 64 bit, this query:
> SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
> SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
> MISDB2A.FASMSCAC_STD_CARRIER_CODE')
> Gets this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access
> this provider through a linked server.
> I works if it is run by the sa login, or by a login that has been granted
> the sysadmin Server Role.
> How can I permission non-sa logins to access the OLE DB provider?|||It has been turned on!
With it left off, EVERYBODY gets this mssage:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
But turned on, uness you have sysadmin privs, you get this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
So, with ad hoc turned on, how do you permission non-sa logins to access the
OLE DB provider?|||Stu (Stu@.discussions.microsoft.com) writes:
> But turned on, uness you have sysadmin privs, you get this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access this provider through a linked server.
> So, with ad hoc turned on, how do you permission non-sa logins to access
> the OLE DB provider?
Books Online gives us this little tidbit:
OPENROWSET can be used to access remote data from OLE DB data sources
only when the DisallowAdhocAccess registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.
i search the registry for DisallowAdhocAccess and I found that under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSSQL.1\Providers
there is a whole list of providers. Of these SQLNLCI has DisallowAdhocAccess
set to 0, but the others not. I did a test with a plain-test user, and
it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
did not have DisallowAdhocAccess listed.
So that seems to be the key.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> i search the registry for DisallowAdhocAccess and I found that under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL.1\Providers
> there is a whole list of providers. Of these SQLNLCI has
> DisallowAdhocAccess
> set to 0, but the others not. I did a test with a plain-test user, and
> it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
> did not have DisallowAdhocAccess listed.
Yes, you nailed it. In BOL, it also stands "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option." In addition, there is a funny thing: you can set this
option to 1 through SSMS (Server Objects - Linked Servers - Providers -
properties of a specific provider). When you check the checkbox, SSMS
creates the registry key. However, you cannot set it to 0 with SSMS. When
you uncheck the checkbox, SSMS deletes the key. So the only option to set it
to 0 is with registry editor (ok, or with SMO, I guess). Not very
recommendable.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Yes, you nailed it. In BOL, it also stands "This option controls the
> ability of non-administrators to run ad hoc queries. Administrators are
> not affected by this option." In addition, there is a funny thing: you
> can set this option to 1 through SSMS (Server Objects - Linked Servers -
> Providers - properties of a specific provider). When you check the
> checkbox, SSMS creates the registry key. However, you cannot set it to 0
> with SSMS. When you uncheck the checkbox, SSMS deletes the key. So the
> only option to set it to 0 is with registry editor (ok, or with SMO, I
> guess).
Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
In my opinion this is a bug. Someone has apparently not understood how
this provider option works. I've submitted
https://connect.microsoft.com/SQLSe...=2574
94
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm working now.
Thanks guys, for your research and feedback!|||Voted:-)
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98D4B72B433ADYazorman@.127.0.0.1...
> Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
> In my opinion this is a bug. Someone has apparently not understood how
> this provider option works. I've submitted
> https://connect.microsoft.com/SQLSe...=25
7494
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Non-Paged Memory leak

One of our application which uses ODBC to access SQL2000
Sp3 database (on the same machine) is leaking Non-paged
Pool memory (continuously). Can this be due to ODBC driver
problem as reported in article "814410 FIX"?
THis is happening only on few machines (SQL2000 Sp3 on
Win2K advanced Server Sp4).
Will SQL2000 Sp3a solve this problem?
I am not able to consistantly see the problem.
We observed NonPaged leak on Task Monitor after we saw
error with Event id 2019 in an event log of a machine
earlier.Dinesh
SP3a does contain a fix for 814410. See:
http://support.microsoft.com/?kbid=306908
Hope this helps.
Sal Terillo
"Dinesh" <dineshks@.infosys.com> wrote in message
news:0ac301c3617b$25682600$a501280a@.phx.gbl...
> One of our application which uses ODBC to access SQL2000
> Sp3 database (on the same machine) is leaking Non-paged
> Pool memory (continuously). Can this be due to ODBC driver
> problem as reported in article "814410 FIX"?
> THis is happening only on few machines (SQL2000 Sp3 on
> Win2K advanced Server Sp4).
> Will SQL2000 Sp3a solve this problem?
> I am not able to consistantly see the problem.
> We observed NonPaged leak on Task Monitor after we saw
> error with Event id 2019 in an event log of a machine
> earlier.
>sql

None-Domain server cannot access SQL2005 data on Windows 2003 domain server

I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.

- I have try following the KB265808 - no success.

- Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.

Error Message:

TITLE: Connect to Server

Cannot connect to ardsqldatawh.

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

Question: Could Windows 2003 security be blocking access? I'm using sa account to access.

Also, sa account does not seems to work for remote access. It is ok when accessing locally.

Any help would be appreciated.

949jc

Hi 959jc,

Have you configured the Windows firewall on your server machine to allow remote connections? If you're intending to use Named Pipes, then you will need to enable the "File and Print sharing" exception. Also, please take a look at this topic in the BOL for additional info regarding configuring firewalls: http://msdn2.microsoft.com/en-us/library/ms175043.aspx.

Another consideration is whether you've restarted your instance after enabling remote protocols.

Otherwise, please provide your connection string and the contents of your error log after you've restarted the service.

Thanks,
Il-Sung.

non-admin user and SSIS (integration services)

I wish to grant access to SSIS (e.g., register server in mssms) for a
user _without_ making that user an administrator of the host.
How can this be done--if at all?
horseradish,
A login that can use SSIS needs no special server rights. In terms of
server roles, my developers are in 'public'. There are decisions that you
need to make, of course, and these are outlined here:
http://technet.microsoft.com/en-us/library/ms137833.aspx
FWIW, I (despite the pitch for how good it is to store packages in msdb)
keep my stored on the file system. For one thing, on the file system more
than one team member can edit the package.
RLF
"horseradish" <dontcallme@.illcallu.net> wrote in message
news:OgC5bzRMIHA.5240@.TK2MSFTNGP04.phx.gbl...
>I wish to grant access to SSIS (e.g., register server in mssms) for a user
>_without_ making that user an administrator of the host.
> How can this be done--if at all?
|||You can follow this procedure to give access to SSIS to users who are not
local Administrators (look for the 'Access is Denied' section').
http://technet.microsoft.com/en-us/library/aa337083.aspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"horseradish" wrote:

> I wish to grant access to SSIS (e.g., register server in mssms) for a
> user _without_ making that user an administrator of the host.
> How can this be done--if at all?
>
|||Ben,

> You can follow this procedure to give access to SSIS to users who are not
> local Administrators (look for the 'Access is Denied' section').
> http://technet.microsoft.com/en-us/library/aa337083.aspx
BINGO. This solved the issue. Thanks for the assist!

non-admin user and SSIS (integration services)

I wish to grant access to SSIS (e.g., register server in mssms) for a
user _without_ making that user an administrator of the host.
How can this be done--if at all?horseradish,
A login that can use SSIS needs no special server rights. In terms of
server roles, my developers are in 'public'. There are decisions that you
need to make, of course, and these are outlined here:
http://technet.microsoft.com/en-us/library/ms137833.aspx
FWIW, I (despite the pitch for how good it is to store packages in msdb)
keep my stored on the file system. For one thing, on the file system more
than one team member can edit the package.
RLF
"horseradish" <dontcallme@.illcallu.net> wrote in message
news:OgC5bzRMIHA.5240@.TK2MSFTNGP04.phx.gbl...
>I wish to grant access to SSIS (e.g., register server in mssms) for a user
>_without_ making that user an administrator of the host.
> How can this be done--if at all?|||You can follow this procedure to give access to SSIS to users who are not
local Administrators (look for the 'Access is Denied' section').
http://technet.microsoft.com/en-us/library/aa337083.aspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"horseradish" wrote:
> I wish to grant access to SSIS (e.g., register server in mssms) for a
> user _without_ making that user an administrator of the host.
> How can this be done--if at all?
>|||Ben,
> You can follow this procedure to give access to SSIS to users who are not
> local Administrators (look for the 'Access is Denied' section').
> http://technet.microsoft.com/en-us/library/aa337083.aspx
BINGO. This solved the issue. Thanks for the assist!

non-admin user and SSIS (integration services)

I wish to grant access to SSIS (e.g., register server in mssms) for a
user _without_ making that user an administrator of the host.
How can this be done--if at all?horseradish,
A login that can use SSIS needs no special server rights. In terms of
server roles, my developers are in 'public'. There are decisions that you
need to make, of course, and these are outlined here:
http://technet.microsoft.com/en-us/...y/ms137833.aspx
FWIW, I (despite the pitch for how good it is to store packages in msdb)
keep my stored on the file system. For one thing, on the file system more
than one team member can edit the package.
RLF
"horseradish" <dontcallme@.illcallu.net> wrote in message
news:OgC5bzRMIHA.5240@.TK2MSFTNGP04.phx.gbl...
>I wish to grant access to SSIS (e.g., register server in mssms) for a user
>_without_ making that user an administrator of the host.
> How can this be done--if at all?|||You can follow this procedure to give access to SSIS to users who are not
local Administrators (look for the 'Access is Denied' section').
http://technet.microsoft.com/en-us/...y/aa337083.aspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"horseradish" wrote:

> I wish to grant access to SSIS (e.g., register server in mssms) for a
> user _without_ making that user an administrator of the host.
> How can this be done--if at all?
>|||Ben,

> You can follow this procedure to give access to SSIS to users who are not
> local Administrators (look for the 'Access is Denied' section').
> http://technet.microsoft.com/en-us/...y/aa337083.aspx
BINGO. This solved the issue. Thanks for the assist!

Non windows clients

What about non windows clients (mac, linux etc)? Can they access Reporting Services with personal permissions/settings as long as they are given a windows account on the RS-machine/domain?

Or... do I have to implement custom security in order to give access to non-windows users?

Regards Andreas

At its simplest, the clients can be given access the Report Manager. You can configure the Report Manager for basic authentication and browser would prompt the user for credentials. Once the user enters the credentials of the local Windows account, she will be given access to the Report Server based on the permissions this account has to the report catalog.|||

Thanks!

That's exactly what I wanted to know. It'll be enought for me!

// Andreas

Non replicated tables and MS Access

What happens whey you try to edit the data in these
tables - any error messages etc? How are you editing
them - using Enterprise manager? (If so you must move off
the line for the edit to be committed).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Manage to find the problem. An index was missing from the table which
prohibited MS Access from updating a row.
Howevere I was always able to edit a row using MS Query Analyser
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:2b2201c49ee7$e565e230$a301280a@.phx.gbl...
> What happens whey you try to edit the data in these
> tables - any error messages etc? How are you editing
> them - using Enterprise manager? (If so you must move off
> the line for the edit to be committed).
> Regards,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Non domain user receive access denied error

I am trying to run a VBScript that uses a SQL user to access a 2000 SQL SP4
clustered instance.
The script runs successfully only when I am logged on with a domain account.
What can the reason for this be?
Thanks in advance,
Ido Friedman
My first thought is that the VBScript is using NT authentication instead of
SQL Authentication.
Can you post the script?
Keith Kratochvil
"Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
news:A1B97509-42C7-4EBB-9BA3-786FA81E682F@.microsoft.com...
>I am trying to run a VBScript that uses a SQL user to access a 2000 SQL SP4
> clustered instance.
> The script runs successfully only when I am logged on with a domain
> account.
> What can the reason for this be?
>
> Thanks in advance,
> Ido Friedman

NON DOMAIN AND DOMAIN CONNECTION

Hi all,

it happen to me a strange problem:

i have a mdb file (in Access 2K) with SQL Server 2K linked tables who
runs on a workstation which is on a different domain that the SQL
Server. It works.

If i create a mdb file from a workstation which is a the domain of the
SQL Server and then i run it a my non-domain workstation i have error
message:

Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection

But if i reattached my tables it works.

If someone have an idea...

PS: same ODBC on both machinesRavieR (principle@.caramail.com) writes:
> it happen to me a strange problem:
> i have a mdb file (in Access 2K) with SQL Server 2K linked tables who
> runs on a workstation which is on a different domain that the SQL
> Server. It works.
> If i create a mdb file from a workstation which is a the domain of the
> SQL Server and then i run it a my non-domain workstation i have error
> message:
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection

I'm nor sure that I followed the confiuguration exactly, but trusted
connections in workgroups is usually an iffy thing. For instance, at
home I have a non-domain workstation, on which I run a non-domain
virtual machine where I have the beta version of SQL 2005. From the
host machine I cannot conenct with trusted connection to SQL2005 on
the virtual machine, but not the other way round.

I think you need to be logged with the same username on both, and the
two users need to have the same password.

You may be better off with SQL authentication in this case.

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

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

Non- Admin connection

I am testing an Access Database on network workstations with linked tables
to SQL Server. I set up a DSN and have no problem logged in as myself with
Admin rights to the server. But when I login as a user with no admin rights
I am denied access with this message:
Connection failed:
SQL Server State '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'dayoubunc1\Sales'.
The network server is Windows 2003 Server, the workstation is Windows 2000
Pro and the SQL Server is a runtime version which came with a Veratis backup
system.
The purpose of moving the tables to SQL Server is security. A user can
steal an Access backend, so I don't want the user to have admin rights to
use the DB. What can I do.
Thanks and God Bless,
Mark A. Sam
Are you sure that it is related to admin rights? Maybe you just need to add a user named "Sales" to
the SQL Server permissions?
"Mark A. Sam" <msam@.Plan-It-Earth.Net> wrote in message
news:u2oqViHmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am testing an Access Database on network workstations with linked tables
> to SQL Server. I set up a DSN and have no problem logged in as myself with
> Admin rights to the server. But when I login as a user with no admin rights
> I am denied access with this message:
> Connection failed:
> SQL Server State '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'dayoubunc1\Sales'.
>
> The network server is Windows 2003 Server, the workstation is Windows 2000
> Pro and the SQL Server is a runtime version which came with a Veratis backup
> system.
> The purpose of moving the tables to SQL Server is security. A user can
> steal an Access backend, so I don't want the user to have admin rights to
> use the DB. What can I do.
> Thanks and God Bless,
> Mark A. Sam
>
|||Hello Scot,
I don't know how I would do that. I opened Enterprise manager and tried to
add a new user but the Network users weren't available. I am very
inexperienced with SQL Server, but I think I recall from another client db
that the users network users were listed. And thinking about they, they had
many users who only had to log in and the DB worked, even through terminal
service sessions. It seems as though I only set up the DSNs on those
workstations.
God Bless,
Mark
|||I tried adding full Control to the user for the SQL Server Folder as well as
the database folder, but it didn't get me any further. Authenticated Users
also have access to the SQL Folder.
Could it me that it is a Run Time Version (that is what I was told) of SQL
Server? Does the client need to purchase the full version?

Non- Admin connection

I am testing an Access Database on network workstations with linked tables
to SQL Server. I set up a DSN and have no problem logged in as myself with
Admin rights to the server. But when I login as a user with no admin rights
I am denied access with this message:
Connection failed:
SQL Server State '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'dayoubunc1\Sales'.
The network server is Windows 2003 Server, the workstation is Windows 2000
Pro and the SQL Server is a runtime version which came with a Veratis backup
system.
The purpose of moving the tables to SQL Server is security. A user can
steal an Access backend, so I don't want the user to have admin rights to
use the DB. What can I do.
Thanks and God Bless,
Mark A. SamAre you sure that it is related to admin rights? Maybe you just need to add
a user named "Sales" to
the SQL Server permissions?
"Mark A. Sam" <msam@.Plan-It-Earth.Net> wrote in message
news:u2oqViHmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am testing an Access Database on network workstations with linked tables
> to SQL Server. I set up a DSN and have no problem logged in as myself with
> Admin rights to the server. But when I login as a user with no admin righ
ts
> I am denied access with this message:
> Connection failed:
> SQL Server State '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'dayoubunc1\Sales'.
>
> The network server is Windows 2003 Server, the workstation is Windows 2000
> Pro and the SQL Server is a runtime version which came with a Veratis back
up
> system.
> The purpose of moving the tables to SQL Server is security. A user can
> steal an Access backend, so I don't want the user to have admin rights to
> use the DB. What can I do.
> Thanks and God Bless,
> Mark A. Sam
>|||Hello Scot,
I don't know how I would do that. I opened Enterprise manager and tried to
add a new user but the Network users weren't available. I am very
inexperienced with SQL Server, but I think I recall from another client db
that the users network users were listed. And thinking about they, they had
many users who only had to log in and the DB worked, even through terminal
service sessions. It seems as though I only set up the DSNs on those
workstations.
God Bless,
Mark|||I tried adding full Control to the user for the SQL Server Folder as well as
the database folder, but it didn't get me any further. Authenticated Users
also have access to the SQL Folder.
Could it me that it is a Run Time Version (that is what I was told) of SQL
Server? Does the client need to purchase the full version?

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator? All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.Hi,
I guess its by design.No UDF's too :-(
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.Hi,
I guess its by design.No UDF's too :-(
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>