How to configure your SQL database in SQL2005 Express

Installing SQL 2005 Express

Nothing special needed here apart from one thing. During install SQL Express defaults to a "named instance" and you DO NOT WANT THIS. Make sure you set it to a "default instance". If you have installed already and are not sure check the service in your services control manager, it should say "SQL Server (MSSQLSERVER)" if correct and if it has "EXPRESS" in there anywhere then you have a named instance. Uninstall and do it again if you see "EXPRESS" in there.

Add the databases
Ok, lots of people say copy mdf file to blah or use the "attach database" function with the mdf file and this is not right. The mdf file that could well have been messed with and not the proper backup.

Right-click Backup devices and select New backup device. Give the device a name and select the proper backup, the one without and extension (a .??? in it's name). The one below is the account database but there should be one for the gamedb and cabalcash. I can see no reason why the others are needed as i have not seen any evidence of them being actually used (please correct me if i am wrong there, but i don't use the others and don't have problems).

http://i344.photobucket.com/albums/p...umpy2/sql5.gif

Now do the same for the cabalcash and gamedb (give them their own names though!).

Next create the database to restore to. Right-click Databases and click New Database. Call it "ACCOUNT" (or Gamedb or CabalCash, you need all 3) and click Ok.

Right-click the database and follow the menu into Tasks->Restore->Database.

http://i344.photobucket.com/albums/p...umpy2/sql6.gif

Set it up like shown above (remember the different names) and click the button with the 3 dots on the right opposite "From device".

http://i344.photobucket.com/albums/p...umpy2/sql7.gif
Select "Backup device" and NOT "File" from the backup media dropdown, click Add and select the backup device you attached earlier.

This looks promising if you look at the data at the bottom :D
http://i344.photobucket.com/albums/p...umpy2/sql8.gif

Tick the tickbox shown and then click Options.

http://i344.photobucket.com/albums/p...umpy2/sql9.gif
Make SURE you tick that tickbox or the restore will FAIL.

Do that for all 3 (ACCOUNT, Gamedb and CabalCash) all using the files with no . in the name and you have the proper databases and not some dump done by somebody else!

Configuring the server to accept remote connections

Now run the "SQL Server Surface Area Configuration" tool from the MSSQL folder inthe start menu and check it allows remote connections as shown below...
http://i344.photobucket.com/albums/p...umpy2/sql4.gif
If you make any changes above you need to restart the service. You can do this on the panel above remote connections on the left of the panel.

Configuring permissions for your cabal userid

Now check your cabal userid is set up like this (the password policy option, default database and language don't matter)...
http://i344.photobucket.com/albums/p...umpy2/sql1.gif
(if you have not set up a user yet when you right-click "Logins" and select New instead of right-clicking the "cabal" user and clicking Propertries. You will get to the same screen).

If it does then make sure the user has ALL of these tickboxes ticked to give it full access to the database...
http://i344.photobucket.com/albums/p...umpy2/sql2.gif

If the cabal userid doesn't appear on the panel above then add it like this...
http://i344.photobucket.com/albums/p...umpy2/sql3.gif

I'll add more later when i get time (and when i work out how to do the stupid linked server in express :/)

Bump. Mind updating this? I wanna see whether I did everything correctly ><

I do have some bits to add but i need to install 2005 again (i use 2000). Which bits are you wanting to check?

All lol. This could be a sticky once you complete it, since 2005's interface is totally different from 2000's.

Offtopic: I added you on MSN.

@chumpywumpy

you can link server in your sql 2005 by looking at server objects + rightclick + new linked servers.

@jiahao_94 - I only did the guide as i knew people were struggling with 2005 (i use 2000, but i broke the vmware machine with it in and need to reinstall it to finish the guide. I will be on MSN later tonight as i have a small job to do first. I'll be on around 20:00-21:00 GMT.

@ChaosPower - Is that where it is? I feel stupid now lol. I was going to do it via command line as i've worked that method out instead, but your method will be better for the guide. Thanks!

np dood. thanks for making this reference. and nice guide.

This is a nice guide :)

I just wanna share my exp using SQL2005 64bit ( coz my OS vista ultimate 64bit )

1. the 1st thing is proper install of sql 2005 ( this is my order )
a. SQLServer2005_SSMSEE_x64 > SQLEXPR > SQLServer2005SP2-KB921896-
x64-ENU

next u can follow the above cabal database guide above

i simply doing it right and still waiting for other guide..

btw thnx for sharing bro...

I have a much better guide available in pdf format now. Check my sig ;)

Hello Chumpy

first i download ur database file on mediaserver

thats it just file type?

and why it can't add to backup on ur tutorial,
so im add new backup database with file type not backup type

whats different? TQ ur explanation

ps:sorry my bad english

Hello chumpy ! I`ve done everything like you posted and i get error ingame when trying to create a character . Error says : Cannot proceed with this command due to unknown DB error.
Any ideas?

hi all,

i have read and followed all the instruction, but i am using windows vista 64bits and I tried to install Microsoft SQL Server Management Studio Express and it shows error as the in this link...
http://img51.imageshack.us/img51/5333/errormmsql.jpg

anyone has any ideas to solve this such a problem?

somvannda
thanks

---------- Post added at 06:23 PM ---------- Previous post was at 06:16 PM ----------

hi all,

i have read and followed all the instruction, but i am using windows vista 64bits and I tried to install Microsoft SQL Server Management Studio Express and it shows error as the in this link...
http://img51.imageshack.us/img51/5333/errormmsql.jpg

anyone has any ideas to solve this such a problem?

somvannda
thanks

---------- Post added at 06:28 PM ---------- Previous post was at 06:23 PM ----------

hi all,

i have read and followed all the instruction, but i am using windows vista 64bits and I tried to install Microsoft SQL Server Management Studio Express and it shows error as the in this link...
http://img51.imageshack.us/img51/5333/errormmsql.jpg

anyone has any ideas to solve this such a problem?

somvannda
thanks

Are you sure you download the x64 version?

hi all,

i have read and followed all the instruction, but i am using windows vista 64bits and I tried to install Microsoft SQL Server Management Studio Express and it shows error as the in this link...
http://img51.imageshack.us/img51/5333/errormmsql.jpg

anyone has any ideas to solve this such a problem?

somvannda
thanks

are you spamming?!?!

hi chumpywumpy, can you help me with this error:
http://img51.imageshack.us/img51/4678/87202187.th.png
and yes i checked the tickbox

The database uses codepage 936 (Chinese) and you need that installed on the computer to be able to restore the databases. For Win7 this is the "install/uninstall languages" on the "keyboard and languages" tab of the "region and languge" option in the control panel.

http://www.mydigitallife.info/2009/0...-in-windows-7/

Downloads (you want simplefied Chinese):

http://keznews.com/6149_Download_Win...Download_Links

installed simplefied Chinese and i still get the same error, il try restarting my pc, hope it works.

EDIT: restarted and i still get the error, i always get an sql error evreytime i try to make an cabal sv( used till now vista x64, and now win7 x64)...always at restoring, maybe if u know how someone can directly make the databases without restoring, please pm me, thanks for the help.

EDIT2:after looking on the internet for an disparate fix ive seen kreains topic on ragezone and im thinking to quit making this server.

I have Win7x64 and the files restored ok for me, though i had all the language packs installed already. It is possible to create the databases without a restore but it would take many queries and you would still run into the same problem, you can't create Chinese language tables without the right language pack.

You could try the other Chinese (Hong Kong) language pack but i'm 99% sure 936 is in the simplified one.

Try checking the registry keys mentioned here:
http://support.microsoft.com/kb/165051

Mine looks like this:
http://i344.photobucket.com/albums/p...2/codepage.gif

fixed, thanks chumpy, seems that you need winOS with all languages preinstalled, it wont work if u install Chinese after, if u install Chinese after you wont have the specific fonts to it.

Hey chumpy!

Ive decided to try again to get this system running in Vista64.
I done exactly as you showed in the guide above, but im still not getting any further.
I start client and there is no connection between client and SQL server. I can input any names and there is no check at all.

Is there any other way I can check the functionality of the database(s)? Running queries for XP_MD5 and creating an account worked fine.
My client is ok, its the same I use for (XP) server.

Sorry, i meant to update this earlier and forgot :/

In Vista it is almost definitely the firewall stopping it if you can ping centos but the server can't connect to sql. I'm using win7 x64 with 2005 express so there are definitely no issues there (though the firewall gave me grief for a few mins).

You can try this little trick to see if centos can connect to sql or not:
http://www.mediafire.com/download.php?qnzlozqkilo

Drop it in your /root/ folder, chmod 0777 the .sh file and run it. It will try connecting to SQL and test a query using xp)md5 to test both. You should get 1 row back (letters and numbers, md5 for "test").

Turned off vista firewall, no difference. So putty and winscp work fine.

That program returned 'could not SQLConnect'. Also did with sa accout too same thing.
What do you suggest is wrong. My config maybe??

---------- Post added at 10:59 PM ---------- Previous post was at 09:27 PM ----------

Im getting sql error:

Quote:

---

Msg 2601, Level 14, State 1, Procedure cabal_tool_registerAccount, Line 10
Cannot insert duplicate key row in object 'dbo.cabal_auth_table' with unique index 'IX_cabal_auth_table'.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure cabal_tool_registerAccount, Line 16
Cannot insert the value NULL into column 'UserNum', table 'ACCOUNT.dbo.cabal_charge_auth'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)

---

This is to do with XP_MD5?? Should I use 64 bit version??

Also randomly, if I put an incorrect password, I get the error 'account/username not found' which means SQL is checking yes? Which means Im connecting? But will only do this once per running the client.

:grr:

Ok i got it working!!
I turned off vista firewall for one, secondly, i added another option under linkedserver->security and added the 'sa' account and chose 'impersonate'.
This seemed to work but not sure why. I was just fiddling with options!

Hopes this also helps somebody else out there???

Ok edit on this...

I connect to cabal randomly. What I mean is, I will connect one time then not another. So its completly random. Im lost now. Officially!

can someone help me? i got this error while installing database. -_-

Quote:

---

===================================

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "GAMESERVER".
OLE DB provider "MSDASQL" for linked server "GAMESERVER" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).".
OLE DB provider "MSDASQL" for linked server "GAMESERVER" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.". (.Net SqlClient Data Provider)

---