Sql Server 2000 help

Hellm0

Hi there,
I've recently started a project in c# with a SQL database, and having to work from home and being new to SQL server 2000 I'm having problems migrateing the database configurations to my home pc. I'm just wondering, how to I config the SQL server to accept local connections and what might be the correct connection string to use for a local pc. Any help would be appreciated, thanx

Seth Brundle

any use?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingConnectionToSQLServer.asp
http://www.codeproject.com/cs/database/SqlConnectionString.asp

Hellm0

That part of the programs allready built, I've got the database made and the c# interface is built(well nearly) but the problem is I am trying to link the two together, which is proving annoying because when I launch the app the connections being refused by my local sql server. I think its a problem with the server user permissions, as I am using a file called "application.config" to hold all connection data, such as the datasource and login/password. whats happening is the servers rejecting it as an untrusted connection or something so I'm unfortunatly guna have to configure da server to accept connections from a specific login then set the permissions for it, which is going to be a pain as it seems all my user roles are set to have no permissions to use anything( eg. Select insert delete etc..). Any way to make a new user an admin on the db without having to configure the permissions?

Merrion

You can use sp_setdbowner to change who is the database owner but this is not ideal.

Instead I would recommend you grant permissions to user groups (i.e. the NT groups like "Power users", "Authenticated users" etc.) and log in using NT security.

For this latter part your config connection string should look like:-

"Persist Security Info=False;Integrated Security=SSPI;database=PrintMonitor;server=MERRION-CJMTR1B;Connect Timeout=30"

(Obviously change the server name and database name to suit your application)

bonkey

Hellm0 wrote:

... when I launch the app the connections being refused by my local sql server. I think its a problem with the server user permissions, as I am using a file called "application.config" to hold all connection data, such as the datasource and login/password. whats happening is the servers rejecting it as an untrusted connection or something

That sounds like you took the default installation options for MSSQL server which is to allow trusted connections only. If you're trying to provide a username/password as part of your login, you need "Mixed Mode Security" enabled which allows trusted and non-trusted connections.

This can be easily reconfigured using the MSSQL Enterprise Manager, but I'd recommend you look up how to do it in the books online.

so I'm unfortunatly guna have to configure da server to accept connections from a specific login then set the permissions for it, which is going to be a pain as it seems all my user roles are set to have no permissions to use anything( eg. Select insert delete etc..). Any way to make a new user an admin on the db without having to configure the permissions?

Yes, there is. In Enterprise Manager, go and manage the login you've created, and assign it the Server Role 'Server Administrator'. I wouldn't recommend this for anything but a development machine, incidentally...normally, I'd suggest giving the user database-level permissions only by making them a member of the 'DB Owner' security role for the necessary database only.

That, and the books online, should point you in the right direction.

jc

bonkey

Merrion wrote:

Instead I would recommend you grant permissions to user groups (i.e. the NT groups like "Power users", "Authenticated users" etc.) and log in using NT security.

This assumes the application is being coded to use Integrated Connections - as does your connection string.

What the OP seems to be describing is an app being developed using non-trusted connections....

jc

Hellm0

Yeh I've come to the conclusion that I've installed the server to accept windows authenticated logins only as opposed to non trusted(sql) logins, the problem is its gonna be annoying to take the server down an set up a new one with the same data (backup - take down server, reinstall, set up server and "restore" it from file). I'll try the windows auth connection string though, just for development purposes. Cheers for your help gents

Merrion

I think the security mode is set on a server wide basis and is held in the registry.

[ HKEY_LOCAL_MACHINE ]
Branch:-
'SOFTWARE\Microsoft\MSSQLServ*er\MSSQLServer'
Key:-
'LoginMode'
Value {DWORD} :-
2

Further info on MSDN

Hellm0

Merrion that worked great, cheers. Was a bit annoying to try an get this working without knowing if the interface was workin correctly with the server for sure. Now if I can only get it finished....